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

 

proc sort data= G out=G1;
by ID;
run;
proc sort data= P out=P1;
by ID;
run;
Proc sort data=T out=T1;
by ID;
Run;

Data CombinedData;
Merge G1 P1;
By ID;
Run;

Data MCC1;
Set CombinedData (keep= Name ID protocol);
If missing(protocol) then delete;
run;

Data T1;

IDchar = put(ID, $8.);
drop ID;
rename IDchar=ID;
Run;

Data BT;
Merge T1 MCC1;
By ID Name;
If inT1 and inMCC1;
Run;

 

Here is my code above, I've been getting this error for the ID variable where SAS states that it is not defined as character or numeric, so I added in a part of the code above to try to transform the ID variable (IDchar = put(ID, $8.);
drop ID; rename IDchar=ID;). But now my Data BT just shows up blank and no observations are printed or read through SAS. Any help is much appreciated, I am still pretty new to SAS. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@kmardinian wrote:

What's strange is the ID in both excel sheets are in the exact same format. The cells are formatted as "General" and the ID numbers are all 8 numbers long. So I'm not sure why SAS is reading them as different?


PROC IMPORT doesn't do a great job at reading data from Excel. If you can, consider using a text file, csv, instead. Then you can specify the types. Excel doesn't enforce types so it's not a good medium for storing and transferring data. 

View solution in original post

12 REPLIES 12
Reeza
Super User

Verify that your INPUT() conversion worked correctly. I suspect it didn't.

kmardinian
Quartz | Level 8

I used proc import to import the excel datasets and they seem to have imported correctly.

Reeza
Super User

Data BT;
Merge T1 MCC1;
By ID Name;
If inT1 and inMCC1; *<- this line is incorrect, you have no variables created with these names;
Run;

kmardinian
Quartz | Level 8

I see, I assumed sas would pull the variables from the excel spreadsheet. What would be the easiest way to do that? Should I use a Keep statement when merging the two datasets?

Thank you!

Astounding
PROC Star

Let's narrow down the problem a little.  Are these the right issues?

 

  • You have an existing data set named T1, that contains a numeric variable named ID.
  • To be able to merge with other data sets, you need to change ID to character (within the T1 data set).

Does that sound about right?  If so ...

 

First, note that your existing data step that re-generates T1 does not contain a SET statement.  Where is the data supposed to come from?  You might need:

 

data T1;

set T1

IDchar = .......

 

Next, one of these formulas would be better:

 

IDchar = put(ID, z8.);

 

or possibly:

 

IDchar = put(ID, 8.);

IDchar = left(IDchar);

 

You might have to see what ID looks like within MCC1 to determine which of these makes sense.  Whichever one looks right, you still need your DROP and RENAME statements.

kmardinian
Quartz | Level 8

Data T2;
Set T1 (keep= ID Name);
IDchar = put(ID, z8.);
drop ID;
rename IDchar=ID;
Run;

Data BT (keep= ID Name);
Merge T2 MCC1;
By ID;
*If inT2 and inMCC1;
Run;

 

So adding in the set statement I think worked! But it did give me a warning saying "WARNING: Variable MRN has already been defined as numeric." Does that still mean it's ok?

 

But now I have also another issue, when I merge T2 and MCC1 to create the dataset BT, I lose the ID the Names from MCC1...I took out my "if inT2 and inMCC1" statement for now because it is still giving me errors

Reeza
Super User

@kmardinian wrote:

 

So adding in the set statement I think worked! But it did give me a warning saying "WARNING: Variable MRN has already been defined as numeric." Does that still mean it's ok?

 

But now I have also another issue, when I merge T2 and MCC1 to create the dataset BT, I lose the ID the Names from MCC1...I took out my "if inT2 and inMCC1" statement for now because it is still giving me errors


No, it's not ok. You can use the log to find syntax errors, but you need to confirm your data meets what you expect by examining the data. We can't really tell you what's correct or not logically.

 

kmardinian
Quartz | Level 8

LOG:

 

 
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
61
62
63 Data T2;
64 Set T1 (keep= ID Name);
65 IDchar = put(ID, z8.);
WARNING: Variable ID has already been defined as numeric.
66 drop ID;
67 rename IDchar=ID;
68 Run;
 
NOTE: There were 26932 observations read from the data set WORK.T1.
NOTE: The data set WORK.T2 has 26932 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
 
 
69
70
71 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
84
 
The Log isn't giving me too much information, so it probably didn't work in fixing this issue. I'm not really sure what next steps to take
kmardinian
Quartz | Level 8

What's strange is the ID in both excel sheets are in the exact same format. The cells are formatted as "General" and the ID numbers are all 8 numbers long. So I'm not sure why SAS is reading them as different?

kmardinian
Quartz | Level 8

If I were to change ID to just be read as character, where would it best to write that code?

Reeza
Super User

@kmardinian wrote:

What's strange is the ID in both excel sheets are in the exact same format. The cells are formatted as "General" and the ID numbers are all 8 numbers long. So I'm not sure why SAS is reading them as different?


PROC IMPORT doesn't do a great job at reading data from Excel. If you can, consider using a text file, csv, instead. Then you can specify the types. Excel doesn't enforce types so it's not a good medium for storing and transferring data. 

kmardinian
Quartz | Level 8

Ok, thank you! I was able to get it to work.

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
  • 12 replies
  • 5410 views
  • 0 likes
  • 3 in conversation