Help using Base SAS procedures

question on sql dataset and macro variables

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 79
Accepted Solution

question on sql dataset and macro variables

I have the following dataset

startdate      enddate         parameter  parmatervalue

01/31/2000 01/31/2005      nc            10

02/01/2005 01/31/2015      nc            12

01/31/2000 01/31/2005      rval            25

I am trying to write a simple macro or function really that takes in the above dataset and a date say 03/31/2005 and picks all the relevant values for each parameter

The output would be to assign local macro variables their value

So for date= 03/31/2005

nc = 12;

rval = 25

I have more parameters than the ones assigned above..


Thanks so much for your help!


Accepted Solutions
Solution
‎02-21-2014 11:58 AM
Super User
Super User
Posts: 7,050

Re: question on sql dataset and macro variables

If you run it outside of macro it has to create GLOBAL macro variables since there is no other active symbol table.

It should create LOCAL macro variables if you run it inside of a macro. Only reason it wouldn't is if the macro does not already have any local macro variables. Note that if the macro being reference already exists then it will change the value of that macro variable whatever symbol table it is in, the same as a %LET statement would.  You can force it to create local by adding 'L' as the third parameter to CALL SYMPUTX().

View solution in original post


All Replies
Super User
Super User
Posts: 7,050

Re: question on sql dataset and macro variables

%let date='31MAR2005'd ;

data _null_;

  set  have ;

  where startdate <= &date <= enddate;

  call symputx( parameter, parmatervalue);

run;

Frequent Contributor
Frequent Contributor
Posts: 79

Re: question on sql dataset and macro variables

Thanks very much but these produce global macro variables, is there way to change them to local?

Contributor
Posts: 33

Re: question on sql dataset and macro variables

Put befor the previous code.

%local date;

Solution
‎02-21-2014 11:58 AM
Super User
Super User
Posts: 7,050

Re: question on sql dataset and macro variables

If you run it outside of macro it has to create GLOBAL macro variables since there is no other active symbol table.

It should create LOCAL macro variables if you run it inside of a macro. Only reason it wouldn't is if the macro does not already have any local macro variables. Note that if the macro being reference already exists then it will change the value of that macro variable whatever symbol table it is in, the same as a %LET statement would.  You can force it to create local by adding 'L' as the third parameter to CALL SYMPUTX().

Frequent Contributor
Frequent Contributor
Posts: 79

Re: question on sql dataset and macro variables

Thank you very much Tom!

Frequent Contributor
Frequent Contributor
Posts: 79

Re: question on sql dataset and macro variables

Thanks very much Tom. Sorry but I have one more follow up question on this same question If I would like to create an array of values for each parameter specified based on the number of dates For example I have another dataset with all dates.

dataset A

date

01/31/2011

02/28/2011

03/31/2011

etc

and i want to look up in the above parameter table all available parameters in this case there is only two, but there can be more

and store each date in an array date and each corresponding parameter in its own array

for example just showing the pseudocode below

The 1st entry

date[1] =01/31/2011

nc[1] = 12

rval[1]=25

Many Thanks for your help!

Super User
Super User
Posts: 7,050

Re: question on sql dataset and macro variables

What do you mean by an ARRAY?

If you are creating macro variables there is no such thing as an array. A macro variable only holds characters.

Frequent Contributor
Frequent Contributor
Posts: 79

Re: question on sql dataset and macro variables

i mean an array of macro variables.. I guess what I am trying to do is store for each date , all the parameter values in their respective arrays

Something like

%let i = 1;

%put &date&i; *This should be for example be the first date 01/31/2011 from my dataset of all dates*/

%put &nc&i;  *This would be 12

%put &rval&i; *This should be25

Thanks very very much for your help Tom!

Super User
Super User
Posts: 7,050

Re: question on sql dataset and macro variables

I am not a big fan of macro variable "arrays".   Seems like a lot of clutter.  If you could explain what you really want to do there are probably many ways to do it that do not depend on created so many macro variables.

Not hard to do, but the question is how do you know when to increment the index variable?

data _null_;

  set have ;

  where (???????);

  by date ;

  if first.date then i+1;

  call symputx(cats(parameter,i),parameter_value,'L');

run;

Frequent Contributor
Frequent Contributor
Posts: 79

Re: question on sql dataset and macro variables

Thanks a lot Tom! Maybe there is a easier way to address what I am trying to do I have a dataset the below and I also have another dataset B which has a list of dates . I would like to match each of the parameters in the below dataset to each of the dates I have in B. The idea behind it being that each date has a different set of parameters which I then use as an input to another macro. I appreciate all your help!

startdate      enddate      parameter  parmetervalue

01/31/2000 01/31/2005      nc            10

02/01/2005 01/31/2015      nc            12

01/31/2000 01/31/2005      rval            25

Super User
Super User
Posts: 7,050

Re: question on sql dataset and macro variables

Just join the list of dates with the parameters and use the new table to generate the macro calls.

I still don't see how you link the parameters into sets of values.  Perhaps you need another variable in your parameter table?

data parms;

  length parmset startdate enddate 8 parameter $32 parametervalue $200;

  informat startdate enddate mmddyy10.;

  format startdate enddate yymmdd10.;

  input parmset -- parametervalue ;

cards;

1 01/31/2000 01/31/2005 nc 10

1 01/31/2000 01/31/2005 rval 25

2 02/01/2005 01/31/2015 nc 12

;;;;

data dates ;

  length case date 8 ;

  informat date yymmdd10.;

  format date yymmdd10.;

  input case date @@;

cards;

1 2004/02/21 2 2008/01/01

;;;;

proc sql ;

  create table runs as

    select a.*,b.*

    from dates a , parms b

    where a.date between b.startdate and b.enddate

    order by a.case,b.parmset,b.parameter

  ;

quit;

filename code temp;

data _null_;

  set runs;

  by case parmset ;

  file code ;

  if first.parmset then put '%mymacro' / '(' @;

  else put ',' @;

  put parameter '=' parametervalue ;

  if last.parmset then put ');' ;

run;

%include code / source2;

%mymacro

(nc =10

,rval =25

);

%mymacro

(nc =12

);

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 376 views
  • 6 likes
  • 3 in conversation