Hi, I had created a new variable (newid) in dataset 'A' by using concatenating 2 or more fields( which I later deleted from the dataset)
This is how dataset A looks:
oldid | sortid | newid | Othervariable1 | Othervariable2 | Othervariable3 |
34 | 1 | A0101E | |||
56 | 2 | A0102E | |||
78 | 3 | A0102E |
I need to use the newid in dataset A to merge it with dataset B . It will be merged by variable oldid. This is how dataset B looks:
Oldid | othervariables... |
34 | xxx |
34 | xxx |
56 | xxx |
56 | xxxx |
78 | xxxx |
78 | xxx |
37 | xx |
37 | xx |
48 | xx |
48 | xx |
I need to have a final dataset that looks like this ;
Oldid | sortid | newid |
34 | 1 | A0101E |
34 | 1 | A0101E |
56 | 2 | A0102E |
56 | 2 | A0102E |
78 | 3 | A0102E |
78 | 3 | A0102E |
37 | . | |
37 | . | |
48 | . | |
48 | .. |
For the merge ,I need only the oldid , sortind and newid from dataset A, so I created a dataset called temp:
data temp (keep=oldid sortind newid);
set A;
run;
It gives me a warning saying ...WARNING: The variable newid in the DROP, KEEP, or RENAME list has never been referenced ...and the dataset temp has no variable newid.
Could anyone tell me why its doing that?
Any suggestions would be very helpful.
Thanks!
I would double check spelling of the variable. Another possibility is that all values of newid are missing.
A proc sql solution that doesn't require a separate subsetting step.
proc sql;
create table temp as
select b.oldid, a.sortid, a.newid
from b left join a on b.oldid=a.oldid;
quit;
I would double check spelling of the variable. Another possibility is that all values of newid are missing.
A proc sql solution that doesn't require a separate subsetting step.
proc sql;
create table temp as
select b.oldid, a.sortid, a.newid
from b left join a on b.oldid=a.oldid;
quit;
Thanks @ballardw I will work on your suggestions and use the left join...(The 'sortid/sortind' was a typo error here)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.