topic Re: 0 observations after recoding variables in SAS Programming

0 observations after recoding variables

Diana_AdventuresinSAS — Wed, 08 Apr 2015 16:58:27 GMT

Hello!

I’m new to SAS and have recently started transferring Excel files into SAS for analysis. I had some issues with the formats and getting the missing values to be appropriately missing for numeric and character variables (period and space, respectively); after working at it, that part seems fixed. But now I have a serious issue: when I do anything with the data (in a DATA step), it deletes all my observations. I’ve tried creating new variables or assigning missing values, trying different data files, and everytime I do anything to alter the data, it leaves me with 0 observations and one or no variables. I’ve tried all kinds of files with different types of variables and different types of recoding, and I always end up losing all my observations. I’ve used SAS before (with data files that were not transferred from Excel) and I never had this issue but anything dealing with files originally from Excel (and I can’t avoid Excel for my job), this issue keeps happening. Is this an issue because of Excel or something with my recoding? Any suggestions would help! Also using plain language for dummies would be greatly appreciated as my knowledge of SAS and coding is pretty limited.

Below is a proc contents of one of my small datasets and the method I use to transfer Excel files into SAS.

# Variable Type Len Format Informat Label

4 county Char 14 $14 $14 county

5 educ Num 8 BEST. educ

2 gender Char 6 $6 $6. gender

1 personid Num 8 BEST. personaid

3 startdate Char 10 $10. $10. startdate

PROC IMPORT OUT= ILO.demo DATAFILE= "C:\Users...xlsx"

DBMS=xlsx REPLACE;

GETNAMES=YES;

RUN;

Re: 0 observations after recoding variables

Steelers_In_DC — Wed, 08 Apr 2015 17:02:10 GMT

Please give an example of what code you are using that give you an empty dataset. If the data is imported correctly it's subsequent code that is the issue, not the import.

Re: 0 observations after recoding variables

Diana_AdventuresinSAS — Wed, 08 Apr 2015 17:22:13 GMT

Thank you for your reply. Below is how I coded it and the log note I got. In this example, I'm trying to create a sex variable with 1,2 values (gender is currently a character variables with male, female values.

data ilo.demo;

If gender= " " then sex=.;

If gender="male" then sex=1;

If gender="female" then sex=2;

run;

4 data ilo.demo;

6 If gender= " " then sex=.;

7 If gender="male" then sex=1;

8 If gender="female" then sex=2;

10 run;

NOTE: Variable gender is uninitialized.

NOTE: The data set ILO.DEMO has 1 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

The "new" data set's two variables are gender and sex and the observation is space for gender and period for sex. It seems that I'm coding to set everything to missing for those two variables, but why is everything erased? And how I can I code it differently?

Thanks!

Diana

Re: 0 observations after recoding variables

RW9 — Wed, 08 Apr 2015 17:26:24 GMT

I think Mark Johnson is probably on the right lines there. Check the dataset in library ILO called demo. When you open it are there observations? If not then its likely the import failed, or didn't return what you expect. This could be from any number of reasons - exactyl why Excel should not be used in the first place - as that tool is unstructured, and can contain pretty much anything. For instance, special characters, blank rows, titles, pivots etc. all don't fit into a proper structure.

Other things, do you have write access to the library ILO? Does the XLS exist and is it really of type xlsx. Check the log when you run that proc import, does it say anything?

Re: 0 observations after recoding variables

Diana_AdventuresinSAS — Wed, 08 Apr 2015 17:31:04 GMT

The data set in ILO called demo looks exactly as it should. All the variables are there, missing values are properly there, and the import is always successful. It's just after I then want to alter it in SAS to create new variables that it erases everything. See my second comment on the coding example and the error message I get.

Is there any other way an average Joe like me could transfer data from Excel into SAS (but not necessarily directly from one to the other)?

Thanks!

Diana

Re: 0 observations after recoding variables

Steelers_In_DC — Wed, 08 Apr 2015 17:37:35 GMT

I'm not sure if I'm reading this correctly but I think the error is you don't have set statement:

data ilo.demo;

set ilo.demo;

If gender= " " then sex=.;

If gender="male" then sex=1;

If gender="female" then sex=2;

run;

What you probably want to start with is this:

data test;

set ilo.demo;

If gender= " " then sex=.;

If gender="male" then sex=1;

If gender="female" then sex=2;

run;

Re: 0 observations after recoding variables

RW9 — Wed, 08 Apr 2015 17:38:00 GMT

Ah, I hadn't seen your reply when I posted. Then your answer is quite simple, you are missing a set statement:

data ILO.demo;

set ILO.demo;

Otherwise the datastep takes nothiing as input.

Re: 0 observations after recoding variables

Diana_AdventuresinSAS — Wed, 08 Apr 2015 17:47:08 GMT

Thank you!!! The set was the answer. I guess I should read up on sets more. You've made my day!!