topic Re: proc sql loop in SAS Programming

proc sql loop

Mike999 — Tue, 08 Apr 2014 08:28:12 GMT

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

Re: proc sql loop

LinusH — Tue, 08 Apr 2014 08:58:39 GMT

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.

Re: proc sql loop

Mike999 — Tue, 08 Apr 2014 09:14:01 GMT

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

Re: proc sql loop

Ksharp — Tue, 08 Apr 2014 09:59:20 GMT

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

Re: proc sql loop

Mike999 — Tue, 08 Apr 2014 10:28:23 GMT

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 🙂

Re: proc sql loop

Ksharp — Tue, 08 Apr 2014 11:18:44 GMT

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

Re: proc sql loop

ashurbs — Tue, 08 Apr 2014 11:19:01 GMT

Hi,

please see solution in attached file.

thanks,

Re: proc sql loop

LinusH — Tue, 08 Apr 2014 11:55:14 GMT

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.

Re: proc sql loop

Mike999 — Tue, 08 Apr 2014 12:30:23 GMT

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

Re: proc sql loop

Mike999 — Tue, 08 Apr 2014 12:54:34 GMT

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.

Re: proc sql loop

Ksharp — Tue, 08 Apr 2014 13:11:49 GMT

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

Re: proc sql loop

Mike999 — Tue, 08 Apr 2014 13:46:39 GMT

Thanks a lot Ksharp,

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

Re: proc sql loop

ashurbs — Wed, 09 Apr 2014 04:08:02 GMT

It should work now.. cheers

Re: proc sql loop

RW9 — Wed, 09 Apr 2014 08:41:54 GMT

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;