sorting/merging data

[sarab]

Hi everyone,

I started using SAS 9.3 just today so if this is a funny question for you it is not for me I am trying to merge data from 2 different sheets in an excel file( I managed to import them to SAS).  Below is my code and the error I got. Could you please help me fix it? Thanks,

PROC IMPORT OUT= study4s

            DATAFILE= "C:\Users\sbaskentli\Desktop\Dr.Chugani_SAS\study4s.xls"

            DBMS=EXCEL REPLACE;

     SHEET="ut";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

run;

PROC IMPORT OUT= study4s2

            DATAFILE= "C:\Users\sbaskentli\Desktop\Dr.Chugani_SAS\study4s.xls"

            DBMS=EXCEL REPLACE;

     SHEET="mturk";

     GETNAMES=YES;

     MIXED=NO;

     SCANTEXT=YES;

     USEDATE=YES;

     SCANTIME=YES;

run;

/*Merging Data by Subject*/

proc sort data=ut; by uniqueid;

proc sort data=mturk; by uniqueid;

data combined;

  merge ut mturk;

  by uniqueid;

run;

ERROR I GOT:

22   /*Merging Data by Subject*/

23   proc sort data=ut; by uniqueid;

ERROR: File WORK.UT.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

24   proc sort data=mturk; by uniqueid;

ERROR: File WORK.MTURK.DATA does not exist.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

25   data combined;

26     merge ut mturk;

ERROR: File WORK.UT.DATA does not exist.

ERROR: File WORK.MTURK.DATA does not exist.

27     by uniqueid;

28   run;

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.COMBINED may be incomplete.  When this step was stopped there were 0

         observations and 0 variables.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[Anotherdream]

You are sorting datasets that don't exist

Your first output statement is making a dataset, and then you are trying to sort a completely different dataset than what you made in your first step, so the system is telling you you're trying to sort a dataset that doesn't exist anywhere.

[PGStats]

You must refer to the DATASET names, the ones after OUT= in the import steps.

PG

[sarab]

Thank you so much. I have another question after combining I got some errors such as

data combined;

68     merge study4s study4s2;

ERROR: Variable gender has been defined as both character and numeric.

ERROR: Variable english has been defined as both character and numeric.

ERROR: Variable describejeans_1__Description_of_ has been defined as both character and numeric.

I read that SAS doesn't read blanks so I filled all the blanks with 999 in excel before importing here. That's the reason why I am getting those errors but I don't know how to fix it. Any suggestion?

Thanks,

Sara

[PGStats]

SAS does read blank cells as missing values. When exporting data tables, Excel has to decide the type of each column. You can help Excel make the right choice by giving explicit number formats to your numeric columns and text formats to your character columns. If you keep having problems, switch to MIXED=YES. That way any column with apparently mixed data types will be translated to text, which you may convert back to numbers, if desired, within SAS.

PG

[Tom]

Easiest solution is to NOT use Excel as a data entry system.  Databases need to have variables that contain the same datatype for all observations, but Excel is designed for spreadsheet operations where cells are independent of each other.  So PROC IMPORT has to make an educated guess as to what data type to use for each variable based on the values it sees in the current sheet. 

You can help PROC IMPORT make the right guess as to the content of the columns by creating a dummy row with valid values as the first row of data.

You can then later delete the row after using PROC IMPORT.  The example below will force GENDER and ENGLISH variables to be character strings at least as long as 7 and 3 characters respectively.

ID,GENDER,ENGLISH

0,FEMALE,YES

1,,

...