setting a list from a greater table

[ilikesas]

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!

[Reeza]

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.

[ilikesas]

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

[Jagadishkatam]

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);

[Jagadishkatam]

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

[PGStats · Accepted solution]

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)

PG

[ilikesas]

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!

[Patrick]

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.

[Reeza]

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);

[RW9]

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

[Jagadishkatam]

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