BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

7 REPLIES 7
Steelers_In_DC
Barite | Level 11

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.

Diana_AdventuresinSAS
Obsidian | Level 7

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

Steelers_In_DC
Barite | Level 11

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;

Diana_AdventuresinSAS
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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? 

Diana_AdventuresinSAS
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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