DATA Step, Macro, Functions and more

0 observations after recoding variables

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

0 observations after recoding variables

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;


Accepted Solutions
Solution
‎04-08-2015 01:38 PM
Super User
Super User
Posts: 7,997

Re: 0 observations after recoding variables

Posted in reply to Diana_AdventuresinSAS

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.

View solution in original post


All Replies
Valued Guide
Posts: 860

Re: 0 observations after recoding variables

Posted in reply to Diana_AdventuresinSAS

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.

New Contributor
Posts: 4

Re: 0 observations after recoding variables

Posted in reply to Steelers_In_DC

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;

5

6    If gender= " " then sex=.;

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

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

9

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

Valued Guide
Posts: 860

Re: 0 observations after recoding variables

Posted in reply to Diana_AdventuresinSAS

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;

New Contributor
Posts: 4

Re: 0 observations after recoding variables

Posted in reply to Steelers_In_DC

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

Super User
Super User
Posts: 7,997

Re: 0 observations after recoding variables

Posted in reply to Diana_AdventuresinSAS

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? 

New Contributor
Posts: 4

Re: 0 observations after recoding variables

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

Solution
‎04-08-2015 01:38 PM
Super User
Super User
Posts: 7,997

Re: 0 observations after recoding variables

Posted in reply to Diana_AdventuresinSAS

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 377 views
  • 6 likes
  • 3 in conversation