BookmarkSubscribeRSS Feed
Mike999
Calcite | Level 5

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

13 REPLIES 13
LinusH
Tourmaline | Level 20

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.

Data never sleeps
Mike999
Calcite | Level 5

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

Ksharp
Super User

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

Mike999
Calcite | Level 5

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 🙂

Ksharp
Super User

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

Mike999
Calcite | Level 5

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...

LinusH
Tourmaline | Level 20

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.

Data never sleeps
ashurbs
Calcite | Level 5

Hi,

please see solution in attached file.

thanks,


Mike999
Calcite | Level 5

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.

ashurbs
Calcite | Level 5

It should work now.. cheers

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ksharp
Super User

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

Mike999
Calcite | Level 5

Thanks a lot Ksharp,

Sounds good to me, I'll give it a try.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 13 replies
  • 2652 views
  • 2 likes
  • 5 in conversation