Creating SAS data set from Excel data

Creating SAS data set from Excel data

Post by Deppman, Laurie » Fri, 06 Oct 2000 04:00:00



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
reading the data correctly?

92   filename adams
93   dde 'excel|adams!r1c1:r19c61';
94
95   data adams;
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,
      SESSION=excel|adams!r1c1:r19c61,RECFM=V,
      LRECL=256

NOTE: 18 records were read from the infile ADAMS.
      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

Post by David L. Casse » Fri, 06 Oct 2000 04:00:00


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
> reading the data correctly?
[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

Post by charles_s_patri.. » Sat, 07 Oct 2000 09:20:11


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.

Try naming your variables like

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
> reading the data correctly?

> 92   filename adams
> 93   dde 'excel|adams!r1c1:r19c61';
> 94
> 95   data adams;
> 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,
>       SESSION=excel|adams!r1c1:r19c61,RECFM=V,
>       LRECL=256

> NOTE: 18 records were read from the infile ADAMS.
>       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/
Before you buy.

 
 
 

Creating SAS data set from Excel data

Post by Malarkey, P » Sat, 07 Oct 2000 04:00:00


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

 
 
 

1. create data entry from existing SAS data set ???

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

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

2. XBOX =The Gamer's Choice.

3. Base SAS DATA Step (Reading a Cansim Data set into SAS directly)

4. VS help sample viewer unable to load the page

5. Help--import raw data to sas data set

6. Home Area Creation/Sharing

7. Creating SAS Data Sets in MVS

8. Planetarium

9. how to change an existing data set's variables order by using data set statement?

10. Creating hypertext links while exporting SAS data to Excel

11. Excel to SAS data sets

12. SAS V.8 data ---> SAS V.6 Data

13. downloaded data set from CMS-SAS to PC-SAS?