## Creating SAS data set from Excel data

### Creating SAS data set from Excel data

For some reason SAS is reading only the first four variables and only the
first digit of each of the four variables.  It is changing the variable
names by picking up only the letters and not the numbers that preceeds them,
e.g., t m f, not 80t 80m 80f.  Does anyone have any ideas about why it's not

94
96       infile adams lrecl=256 missover firstobs=2;
97
98
99       input age \$ 80t \$ 80m \$ 80f \$ 81t \$ 81m \$ 81f \$ 82t \$ 82m \$ 82f \$
83t \$ 83m \$ 83f \$
100                84t \$ 84m \$ 84f \$ 85t \$ 85m \$ 85f \$ 86t \$ 86m \$ 86f \$ 87t
\$ 87m \$ 87f \$
101                88t \$ 88m \$ 88f \$ 89t \$ 89m \$ 89f \$ 90t \$ 90m \$ 90f \$ 91t
\$ 91m \$ 91f \$
102                92t \$ 92m \$ 92f \$ 93t \$ 93m \$ 93f \$ 94t \$ 94m \$ 94f \$ 95t
\$ 95m \$ 95f \$
103                96t \$ 96m \$ 96f \$ 97t \$ 97m \$ 97f \$ 98t \$ 98m \$ 98f \$ 99t
\$ 99m \$ 99f \$;
104  run;

DDE Session,
LRECL=256

The minimum record length was 214.
The maximum record length was 256.
One or more lines were truncated.
NOTE: The data set WORK.ADAMS has 18 observations and 4 variables.
NOTE: DATA statement used:
real time           0.00 seconds

### Creating SAS data set from Excel data

Laurie Deppman wrote [in part]:
Quote:> For some reason SAS is reading only the first four variables and only the
> first digit of each of the four variables.  It is changing the variable
> names by picking up only the letters and not the numbers that preceeds them,
> e.g., t m f, not 80t 80m 80f.  Does anyone have any ideas about why it's not
[snip]
> 99       input age \$ 80t \$ 80m \$ 80f \$ 81t \$ 81m \$ 81f \$ 82t \$ 82m \$ 82f \$ 83t

\$ 83m \$ 83f \$
[......]

You're forgetting the rules for SAS variable names.  Even in V8 you can't
start the variable name with a number.  So SAS is parsing that line as:
input age \$ 80 t \$ 80 m \$ 80 f \$ 80  ....
which is not what you wanted.  Change your variable names to t80, m80, f80, ....
And you may need to be more specific about the lengths of some of your
strings, if the results don't come out quite as you anticipated.

David
--

Senior computing specialist
mathematical statistician

### Creating SAS data set from Excel data

Laurie,

I believe it is the naming convention of your SAS variable.

All SAS variables can only begin with a non numeric character such as

a thru z, _.  Starting your var names with "80" will cause the "80" to
disppear, and since most of your variables end in a "t", "m", or "f" is
why you only end up with 4 SAS variables.

t80, m80, f80, t81, m81, f81, etc etc

HTH
Charles Patridge

Quote:> For some reason SAS is reading only the first four variables and only
the
> first digit of each of the four variables.  It is changing the
variable
> names by picking up only the letters and not the numbers that preceeds
them,
> e.g., t m f, not 80t 80m 80f.  Does anyone have any ideas about why
it's not

> 94
> 96       infile adams lrecl=256 missover firstobs=2;
> 97
> 98
> 99       input age \$ 80t \$ 80m \$ 80f \$ 81t \$ 81m \$ 81f \$ 82t \$ 82m \$
82f \$
> 83t \$ 83m \$ 83f \$
> 100                84t \$ 84m \$ 84f \$ 85t \$ 85m \$ 85f \$ 86t \$ 86m \$ 86f
\$ 87t
> \$ 87m \$ 87f \$
> 101                88t \$ 88m \$ 88f \$ 89t \$ 89m \$ 89f \$ 90t \$ 90m \$ 90f
\$ 91t
> \$ 91m \$ 91f \$
> 102                92t \$ 92m \$ 92f \$ 93t \$ 93m \$ 93f \$ 94t \$ 94m \$ 94f
\$ 95t
> \$ 95m \$ 95f \$
> 103                96t \$ 96m \$ 96f \$ 97t \$ 97m \$ 97f \$ 98t \$ 98m \$ 98f
\$ 99t
> \$ 99m \$ 99f \$;
> 104  run;

> NOTE: The infile ADAMS is:
>       DDE Session,
>       LRECL=256

>       The minimum record length was 214.
>       The maximum record length was 256.
>       One or more lines were truncated.
> NOTE: The data set WORK.ADAMS has 18 observations and 4 variables.
> NOTE: DATA statement used:
>       real time           0.00 seconds

--
Charles Patridge - PDPC, Ltd.
172 Monce Road - Burlington, CT 06013 USA
Phone: 860-673-9278 or 860-675-9026

Sent via Deja.com http://www.deja.com/

### Creating SAS data set from Excel data

Hi, Laurie!

I have a SAS program where I read an XL spreadsheet via DDE that I wrote a
couple of years ago...  While I don't remember exactly how or why it ended
up as it is, I can tell you that the difference between the log you posted
and my program is that, in addition to the infile and input statements in
the data step, I also have an informat statement where I specify the
characteristics of each field.

I've found that - especially in PC SAS - you sometimes have to fiddle with
what I call the "SAS magic words" to get things to work the way you want.
;-)

Cheers!

- Pat

SASers, I have an existing dataset of scanned surveys
that includes labels and formats. I need to hand enter
surveys that did not meet the scanning deadline.

Is there an way to set up a quick-and-dirty
method of data entry for appending the new
surveys to this data set. (Note: I have
no experience with AF)

Thanks much......Gordon

---------------------------
Gordon Keeler, statistician
Dept. of Psychiatry
Duke University Med Center

---------------------------