topic Re: setting a list from a greater table in SAS Data Management

setting a list from a greater table

ilikesas — Sat, 20 Jun 2015 01:27:16 GMT

Hi,

suppose that I have a big table containing several variables. One of the variables is NAME and another is DATE.

What I want to do is to create a program, perhaps a macro, such that I can select a list of names conditional on date limits.

So the intuition of the code is something like that:

data list;

set table;

if name = (list) and date1 =< date =< date2;

run;

Thank you!

Re: setting a list from a greater table

Reeza — Sat, 20 Jun 2015 02:19:50 GMT

How are the date variables determined? Is date1/date2 constants for all names or do they vary by name And come from a different table.

This type of question is better answered with SQL than a data step.

Re: setting a list from a greater table

ilikesas — Sat, 20 Jun 2015 02:32:41 GMT

date1 and date2 can be something like 21jan2009, and not all names might have any given date

Re: setting a list from a greater table

Jagadishkatam — Sat, 20 Jun 2015 02:33:21 GMT

Please try this untested code

proc sql;

select distinct name into :list1 - :list&sysmaxlong from have;

quit;

%put &list1 &sqlobs;

%macro test;

%do i=1 to &sqlobs;

data &&list&i;

set have;

if name="&&list&i" and date1 =< date =< date2;

run;

%end;

%mend;

%test;

Thanks,

Jag

Re: setting a list from a greater table

PGStats — Sat, 20 Jun 2015 02:52:17 GMT

Assuming you've got a name list in table myList and your big table is called myTable, you could do:

%macro getNames(beginDate,endDate);

proc sql;

create table want as

select * from myTable

where

name in (select name from myList) and

date between "&beginDate"d and "&endDate"d;

quit;

%mend getNames;

%getNames(01JAN2014,31DEC2014);

(untested)

Re: setting a list from a greater table

ilikesas — Sat, 20 Jun 2015 03:17:47 GMT

Hi PG ,

your code worked.

If possible i would like to know hoe to do the following modification:

Instead of '' where name in (...)" is it possible to do ''where name in &list'' because I want to create a code which will make a &list that might contain hundreds of names like this I don't have to input them manually.

Thank you!

Re: setting a list from a greater table

Patrick — Sat, 20 Jun 2015 07:18:32 GMT

The macro variable would need to contain a string like: 'name1', 'name2', ...

But if you can create and populate such a macro variable then what holds you back to create a table instead and use it in the sub-select as proposed by ? Using a table has the advantage that you're not at risk to hit the maximum length of a macro variable.

Re: setting a list from a greater table

Reeza — Sat, 20 Jun 2015 18:08:42 GMT

myList is a second table with the list of names you want to subset on.

Otherwise its change the where condition to name in (&LIST) if you want that method. However &List has to be less than 64K chars and it would take an extra step to generate it.

%macro getNames(beginDate,endDate, List);

proc sql;

create table want as

select * from myTable

where name in (&List)

and

date between "&beginDate"d and "&endDate"d;

quit;

%mend getNames;

%getNames(01JAN2014,31DEC2014);

Re: setting a list from a greater table

Jagadishkatam — Mon, 22 Jun 2015 01:23:20 GMT

I modified the code a bit to accomodate the logic by including the two date parameters. Could you please test this and let me know if it works for your case.

proc sql;

select distinct name into :list1 - :list&sysmaxlong from have;

quit;

%put &list1 &sqlobs;

%macro test(beginDate,endDate);

%do i=1 to &sqlobs;

data &&list&i;

set have;

if name="&&list&i" and "&beginDate"d =< date =< "&endDate"d;

run;

%end;

%mend;

%test(01JAN2014,31DEC2014);

Re: setting a list from a greater table

RW9 — Mon, 22 Jun 2015 10:50:12 GMT

Put your list of values in a dataset and use where in (select var from yourlistofvalues). If you put your list in a macro variable you will run out of space and your code will be unnecessarily complicated.

@Jagadishkatam, would your code not create one dataset per item in the list? I don't think this is the intent ( plus you would create &sqlobs. * datasteps).

Re: setting a list from a greater table

Jagadishkatam — Mon, 22 Jun 2015 14:48:09 GMT

Thank you for the suggestion.

I believe this will be the right code for @ilikesas purpose.

/*This sql step will create a macro variable with a list of names in quotes separated by the blank space for use in the next code which creates the dataset*/

proc sql;

select distinct quote(name) into :list separated by ' ' from have;

quit;

%put &list &sqlobs;

%macro test(beginDate,endDate);

data want;

set have;

if name in ("&list") and "&beginDate"d =< date =< "&endDate"d;

run;

%mend;

%test(01JAN2014,31DEC2014);

Thanks,

Jag