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!
@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.
Verify that your INPUT() conversion worked correctly. I suspect it didn't.
I used proc import to import the excel datasets and they seem to have imported correctly.
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;
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!
Let's narrow down the problem a little. Are these the right issues?
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.
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
@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.
LOG:
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?
If I were to change ID to just be read as character, where would it best to write that code?
@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.
Ok, thank you! I was able to get it to work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.