DATA Step, Macro, Functions and more

Unable to match a macro variable with datastep variable

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Unable to match a macro variable with datastep variable

I am unable to successfully compare a macro variable with a character variable in a data set. The %if loop never turns "TRUE". Kindly advice.

/*create dummy data*/

data datefile;

input tran_date date9. cnt 3.;

datalines;

13feb2015 5

10feb2015 4

11feb2015 3

05feb2015 8

08feb2015 5

01jan2015 1

20dec2014 1

31jan2015 2

23dec2014 2

12jan2015 1

;

/*calculate month*/

data datefile11;

set datefile;

tran_mon=year(tran_date)*100+month(tran_date);

run;

/*select distinct month*/

proc sql;

create table datefile12 as select distinct(tran_mon)

from datefile11 order by tran_mon;

quit;

/*convert month from numeric to character*/

data datefile11(drop=tran_mon);

informat tran_mon2 $6.;

set datefile11;

tran_mon2=tran_mon;

run;

/*create macro variables through datastep*/

data datefile13;

set datefile12;

monum = cat('mnth',_N_);

run;

data _null_;

set datefile13;

call symput(monum,trim(left(tran_mon)));

run;

/*use array to make separate column for each month and

put split count for each month to each colunms*/

%macro c;

proc sql noprint;

select count(1) into :nrow from datefile13;

quit;

%let nrow = &nrow;

data datefile14;

set datefile11;

array mon{*} mon_1 - mon_&nrow;

%do i=1 %to &nrow;

  %if tran_mon2 = &&mnth&i %then %do; %put tran_mon2;

                                         mon_&i = cnt; %end;

  %else %do; mon_&i = 0 ; %end;

%end;

run;

%mend c;

%c


Accepted Solutions
Solution
‎02-15-2015 08:37 PM
Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

So from the MIN and MAX date you can calculate the number of months.  Note that this will also handle the cases where there months in the interval with no data.

To make your matrix just loop over the whole array setting it to zero and then set the one entry that the current date represents to the value of CNT.

proc sql noprint ;

  select min(tran_date),max(tran_date)

    into :mindt,:maxdt

    from datefile

  ;

quit;

%let nmonths=%eval(%sysfunc(intck(month,&mindt,&maxdt))+1);

data want ;

  set datefile ;

  array month (&nmonths) ;

  do _n_=1 to &nmonths; month(_n_)=0; end;

  month(intck('month',&mindt,tran_date)+1)=cnt;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

What are you actually trying to do?

You probably do not need macro code at all.

Macro code generates SAS code that is then compiled and run.

Your %IF statement is comparing the constant text tran_mon2 to the values of macro variables with names like MNTH1, MNTH2 etc.  It is definitely NOT comparing the value of the datastep variable with the name TRAN_MON2 to any macro variable values since the %IF will have finished executing before the DATA step starts.

Plus I do not see any place in your program where these macro variables have been created.

Contributor
Posts: 36

Re: Unable to match a macro variable with datastep variable

Hi Tom,

Thanks for your reply. I am trying to create a new column for each new month and and trying to do it with macros and arrays, instead of proc transpose (as I have to take care of multiple fields like count).

macro_var.jpg

mon_1 is for 201412. mon_2 is 201501 and mon_3 is 201502.

So in the first row mon_3 should be 5. In second row mon_3 should be 4.


Kindly advice how to achieve the same. I have do the same for multiple like mon_1_spend, mon_1_tran, mon_1_credit etc..

Solution
‎02-15-2015 08:37 PM
Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

So from the MIN and MAX date you can calculate the number of months.  Note that this will also handle the cases where there months in the interval with no data.

To make your matrix just loop over the whole array setting it to zero and then set the one entry that the current date represents to the value of CNT.

proc sql noprint ;

  select min(tran_date),max(tran_date)

    into :mindt,:maxdt

    from datefile

  ;

quit;

%let nmonths=%eval(%sysfunc(intck(month,&mindt,&maxdt))+1);

data want ;

  set datefile ;

  array month (&nmonths) ;

  do _n_=1 to &nmonths; month(_n_)=0; end;

  month(intck('month',&mindt,tran_date)+1)=cnt;

run;

Contributor
Posts: 36

Re: Unable to match a macro variable with datastep variable

Thanks a lot Tom!! Two queries/observations -

1) The macro processing always occurs before data step processing(as mentioned by you too in the first reply). So is there a way to compare dataset variable value (and not the name text) with a macro variable for comparison? Is there a way to may be use a 'symget' statements?

2) I wasn't aware of naming variables with the help of functions. Thanks for showing me the same.

Thanks Again!!

Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

You can compare the value of a variable to a macro variable the same way you would to any other literal value.

data x;

set y;

if charvar = "&mvar1" then ....

if numvar = &mvar2 then ....

run;

Make sure to use double quotes when comparing to a character variable as macro references are not resolved inside of single quotes.

Naming of variables?  Not sure what you mean. 

1)You can use the %SYSFUNC() macro function to call almost any SAS function. 

2) SAS will let you use almost any valid name for a variable or data set name.  It will give you a warning if it thinks you might get yourself confused.  For example I used the name MONTH as the name of the array and SAS will complain because there is a MONTH() function also.  So I could not use the MONTH() function in that program since SAS it is being interpreted as an array reference.

3) If you define an array without a list of variable SAS will automatically name the variables to match the array.  ARRAY X (10) ;  will make ten variable X1 to X10.

Contributor
Posts: 36

Re: Unable to match a macro variable with datastep variable

Thanks Tom.

Sorry, Another question .. Smiley Sad

If I want to rename my fields as month_201412_cnt, month_201501_cnt etc.. can it be done in this step?

I can use a 'RENAME' later on, but was wondering if it can be done here itself!!

Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

You would need to generate those names into a macro variable.

If there are no gaps in the months then you could generate the names from the unique months in the data. 

select distinct catx('_','month',substr(put(tran_date,yymmddn8),1,6),'cnt')

into :cnt_vars separated by ' '

from datefile

;

...

array month &cnt_vars ;

But if there are gaps then you need to generate names of all of the months in the interval (otherwise the calculated index based on number of months since index date will not work).

data _null_;

length cnt_vars $32000 ;

do i=0 to &nmonths-1 ;

   cnt_vars = catx(' ',cnt_vars,catx('_','month',substr(put(tran_date,yymmddn8),1,6),'cnt'));

end;

call symputx('cnt_vars',cnt_vars);

run;

Contributor
Posts: 36

Re: Unable to match a macro variable with datastep variable

Thanks Tom!!

I opened another thread with this query -

So copying your answer there.

Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

Do you just want to summarize CNT by Month?

Why not just use PROC FREQ?

proc freq ;

  tables tran_date / out=count ;

  weight cnt ;

  format tran_date yymm7. ;

run;

You could then TRANSPOSE it to get counts into different variables.

proc transpose data=count out=want(drop=_name_ _label_) prefix=MONTH_;

  var count ;

run;

proc print data=want; run;

You might need to watch out for missing months .

Super User
Super User
Posts: 6,500

Re: Unable to match a macro variable with datastep variable

Do you want to count with your own code instead of using a procedure?

If you take the range of months using INTCK() function you can do this with a macro.  This will also handle the creation of values for the missing months in the middle of the range.

proc sql noprint ;

  select min(tran_date),max(tran_date)

    into :mindt,:maxdt

    from datefile

  ;

quit;

%let nmonths=%eval(%sysfunc(intck(month,&mindt,&maxdt))+1);

data want ;

  array month (&nmonths) ;

  do until (eof);

    set datefile end=eof;

    month(intck('month',&mindt,tran_date)+1)+cnt;

  end;

  keep month: ;

  output;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 517 views
  • 6 likes
  • 2 in conversation