BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rajat051984
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Tom
Super User Tom
Super User

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.

rajat051984
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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;

rajat051984
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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.

rajat051984
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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;

rajat051984
Fluorite | Level 6

Thanks Tom!!

I opened another thread with this query -

So copying your answer there.

Tom
Super User Tom
Super User

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 .

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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