Hi friends, I need to do the following and I was wondering whether I could write a macro with a proc sql loop inside or something like that:
- I have a data set in which one of the columns is a the name of a data set
- I need to do a left join in a proc sql with that data set in the column I mentioned to retrieve a date for each person in the original data set
So, the data set I have looks like this:
person1 dataset1
person2 dataset2
person3 dataset1
person4 dataset1
person5 dataset3
......
And I need to append a column to this dataset with a date for each person. And that date is located in the corresponding dataset.
Now, how can I do that?
Hope you can understand what I'm trying to do.
Thank you very much in advance,
--Mike
Not exactly sure, perhaps some sample input and desired out data would help. And, the underlying requirement.
From the brief description it sounds like you are doing some low level programming - perhaps changing the data model would make your task easier.
Hi LinusH, thanks for your reply.
The thing is easy, maybe I didn't make myself understood.
The dataset I have looks like this:
name dataset
-------- ----------------------
Mike Oct2011_dates
John Nov2012_dates
Susan Mar2010_dates
Liz Oct2011_dates
........
So, I need to retrieve a date for each of the names, which is in the dataset specified in the column 'dataset'. The date for Mike is in the dataset 'Oct2011_dates'; the date for Susan is in the dataset 'Mar2010_dates', etc...
And at the end, my original dataset will look like this:
name dataset date
-------- ---------------------- ------------------
Mike Oct2011_dates 2010-11-28
John Nov2012_dates 2012-10-12
Susan Mar2010_dates 2009-03-26
Liz Oct2011_dates 2010-07-19
........
Hope it's clear now,
Thanks again.
--Mike
You can union them all together by SET,then do left join.
data have; input name $ dataset : $20.; cards; Mike Oct2011_dates John Nov2012_dates Susan Mar2010_dates Liz Oct2011_dates ; run; data Oct2011_dates; input date : $20. name $; cards; 2010-11-28 Mike ; run; data Nov2012_dates; input date : $20. name $; cards; 2012-10-12 John ; run; data x; length dsname ds $ 20; set Oct2011_dates Nov2012_dates indsname=dsname; ds=scan(dsname,-1,'.'); run; proc sql; create table want as select a.*,b.date from have as a left join x as b on upcase(dataset)= ds and a.name=b.name ; quit;
Ksharp
Message was edited by: xia keshan
Thanks Ksharp.
The thing is that I was looking for a method to do it with a loop inside a macro, in such a way that I can pass the dataset name as an argument to the sql proc, because I have about 150.000 names (well, actually id numbers) and maybe 70 or 80 different datasets to look for the date 🙂
OK. That be more simple.
%macro match(dsname=); data x; set &dsname ; retain ds "&dsname"; run; proc sql; create table want as select a.*,b.date from have as a left join x as b on dataset= ds and a.name=b.name ; quit; %mend match; %match(dsname=Oct2011_dates)
Xia Keshan
Yes, LinusH, but in this way I must call the macro as many times as different datasets (with dates) I have.
What I was trying to find is a way of forcing the macro to take the dataset itself each time, since I already know in which dataset to look for the date, for a given person.
Don't know, maybe I'm trying to complicate things, but i cannot figure out a way of doing it without writing loads of proc sqls.
Just trying to automatize it with a macro...
As I might slipped in my first answer, I'm more into the original suggestion. Fetching individual rows might be a useful technique when building OLTP /data entry-type applications - not ideal for data management/reporting. Seems much simpler to keep all data in the same table.
Hi,
please see solution in attached file.
thanks,
Thanks, ashurbs.
Almost, but not quite 😞
I'm running your macro but there are errors in the proc sql inside the loop saying that the macro references for dataset and name weren't resolved. I'm trying to figure it out.
It should work now.. cheers
Hi,
Just to add another method avoiding macros and macro variables:
data want;
set have;
attrib calc_date format=$20.;
run;
data _null_;
set have;
call execute('proc sql;
update WORK.WANT
set CALC_DATE=(select DATE from WORK.'||strip(dataset)||' where NAME="'||strip(name)||'")
where NAME="'||strip(name)||'";
quit;');
run;
And I would use.
%macro match(list=); data x; length dsname ds $ 20; set &list indsname=dsname; ds=scan(dsname,-1,'.'); run; proc sql; create table want as select a.*,b.date format=yymmdd10. from have as a left join x as b on upcase(dataset)= ds and a.name=b.name ; quit; %mend match; data d; input dsn : $20.; cards; Oct2011_dates Nov2012_dates ; run; proc sql; select dsn into : list separated by ' ' from d; quit; %match(list=&list)
Xia Keshan
Thanks a lot Ksharp,
Sounds good to me, I'll give it a try.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.