topic Re: Help with Macro if then else into Case? in SAS Programming

Help with Macro if then else into Case?

kjohnsonm — Thu, 15 Dec 2016 21:17:59 GMT

Hello all,

I have a basic macro if then else, I need to convert into a case statement. Can someone point me to a good reference please?

In my case I am just going out for a given term and snapshot and getting the correct date for use in a single macro var for later use in soft coding my programs.

* So I get my temp dates, out of one record for the term I want,…;

* --- get census date ---;
proc sql noprint;
select term_census_dt into: census_date
from My_DB.xw_term
where strm="&strm.";
quit;
%put &census_date.;

* --- get midterm date ---;
proc sql noprint;
select term_midterm_dt into: midterm_date
from My_DB.xw_term
where strm="&strm.";
quit;
%put &midterm_date.;

* --- get term_end date ---;
proc sql noprint;
select term_end_dt into: term_end_dt
from My_DB.xw_term
where strm="&strm.";
quit;
%put &term_end_dt.;

*…etc …;

* Next I need to pass one of (census, midterm,eot,ceneot) to my macro and get the correct date into my var.l
* for program to unify the code to use the same variable everywhere;

* old code;
/*%let snapshot_date2=&census_date.;*/

* current macro, that needs to be a case/switch;

 %macro date_check;
 %global snapshot_date2;
%if &snapshot2=census %then
    %do;
        %let snapshot_date2=&census_date.;
        %put &snapshot_date2.;
    %end;
%else
    %do;
        %let snapshot_date2=&midterm_date.;
        %put &snapshot_date2.;
    %end;
%mend date_check;
%date_check;

%put &strm.;
%put &snapshot.;
%put &snapshot2.;
%put &snapshot_date2.;

I was trying to modify some code I found on this forum but am failing to get it to work, thus I just thought a fresh start with where I am and what I wanted to do could be a good reboot. TIA -KJ

Re: Help with Macro if then else into Case?

kjohnsonm — Thu, 15 Dec 2016 23:05:10 GMT

Well I would still like to know how to make a macro case statment work, however I have a solution that shortens my line count and is very readable. with a Proc SQL and into and case all at once, in stead of 4 proc sqls and a macro :

options nocenter;
options ls=256;

libname My_DB odbc dsn=My_DB schema = dbo;

%let prog_suffix=;
%let and_snapshot=;
%let strm=2167;

%let snapshot='midterm';
%let snapshot2=midterm;

%let census_date=;
%let midterm_date=;
%let term_end_dt=;
%let snapshot_date2=;

proc sql ;
select 
&snapshot. as snapshot
,term_census_dt ,term_midterm_dt ,term_end_dt 
,case when calculated snapshot = 'midterm' then term_midterm_dt 
     when calculated snapshot = 'census' then  term_census_dt
	 /* etc add other snapshots here */
	 else term_census_dt end as snapshot_date2 format DATETIME22.3
into :census_date, :midterm_date, :term_end_dt, :snapshot_date2
from My_DB.xw_term
where strm="&strm.";
quit;
%put &census_date.;
%put &midterm_date.;
%put &term_end_dt.;
%put &snapshot_date2.;

Output from my put statments like I wanted for later use as a macro var as needed:

I did not know you could use into for more than one field at a time, and I figured if i can get them all at once why not try a case statement in the Proc SQL at the sametime.   cheers. -KJ
snapshot          term_census_dt         term_midterm_dt             term_end_dt          snapshot_date2
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
midterm   02SEP2016:00:00:00.000 12OCT2016:00:00:00.000 16DEC2016:00:00:00.000 12OCT2016:00:00:00.000

Re: Help with Macro if then else into Case?

ChrisNZ — Fri, 16 Dec 2016 00:30:15 GMT

Like this (2 solutions shown: one in proc sql and one outside) ?


%macro test(snapshot,strm);

  %global census_date midterm_date term_end_dt snapshot_date2;

  proc sql noprint;
    select TERM_CENSUS_DT 
          ,TERM_MIDTERM_DT 
          ,TERM_END_DT 
          ,%if &snapshot. = midterm %then TERM_MIDTERM_DT; 
           %if &snapshot. = census  %then TERM_CENSUS_DT ;
           %else                          TERM_CENSUS_DT ; as SNAPSHOT_DATE2 format datetime22.3
      into :census_date, :midterm_date, :term_end_dt, :snapshot_date2
    from MY_DB.XW_TERM
    where STRM="&strm.";
  quit;

%mend;

%macro test(snapshot,strm);

  %global census_date midterm_date term_end_dt snapshot_date2;

  proc sql noprint;
    select TERM_CENSUS_DT 
          ,TERM_MIDTERM_DT 
          ,TERM_END_DT 
      into :census_date, :midterm_date, :term_end_dt
    from MY_DB.XW_TERM
    where STRM="&strm.";
  quit;

  %if &snapshot. = midterm %then %let snapshot_date2=&term_midterm_dt; 
  %if &snapshot. = census  %then %let snapshot_date2=&term_census_dt ;
  %else                          %let snapshot_date2=&term_census_dt ; 

%mend;


%test(midterm,2167);

%put &=census_date;
%put &=midterm_date;
%put &=term_end_dt;
%put &=snapshot_date2;

Re: Help with Macro if then else into Case?

RW9 — Fri, 16 Dec 2016 10:30:48 GMT

Why do you need all the macro code? One of SQL's biggest powers is the ability to both join data and to sub-query. Using these techniques you can easily add your date into your data without having all the code:

%let term=midterm;

proc sql;
  create table WANT as
  select  *,
          (select TERM_&term._DT from MY_DB.XW_TERM where STRM="&term.") as DATE_I_WANT
  from    HAVE;
quit;

Note, I am only guessing what your doing (no test data/required output) to do a proper evaluation.

Re: Help with Macro if then else into Case?

kjohnsonm — Fri, 16 Dec 2016 16:01:35 GMT

lol, "unlimited power"

Re: Help with Macro if then else into Case?

kjohnsonm — Fri, 16 Dec 2016 16:10:50 GMT

Well in short if I can do a "if then else in a macro" I want to know how to do a "case", for three or more choice options it is as simple as that. I cannot seem to find an example that is a straight forward change of the original macro to a case macro. What I am really doing is automating the way my programs startup and get their parameters automatically from a few crosswalk tables that are very stable and extremely accurate to help control the way the rest of my programs operate. But in some cases I cannot "lookup" the data I have to just assign it from a list of options. -KJ

Re: Help with Macro if then else into Case?

ballardw — Fri, 16 Dec 2016 21:11:14 GMT

Can you show what the actual BASE SAS code is supposed to be? If you don't know what the "case statement" would look like as part of a Proc SQL step then it's a bit hard to get there.

Re: Help with Macro if then else into Case?

kjohnsonm — Wed, 21 Dec 2016 16:08:32 GMT

Well that is the problem, I don't know if it is even posible for starters and I have not yet seen one like I am hoping to do not inside a Proc SQL statments.

Here is my sample miltiple part "if then else" that works, that I would love to see a case or swith for:

%macro date_check;
 %global snapshot_date2;
%if &snapshot2=midterm %then 
	%do;
		%let snapshot_date2=&midterm_date.;
		%put &midterm_date.| " midterm_date " ;
	%end;

%else %if &snapshot2=census %then 
	%do;
		%let snapshot_date2=&census_date.;
		%put &census_date.| " census_date " ;
	%end;

%else %if &snapshot2=ceneot %then 
	%do;
		%let snapshot_date2=&term_end_snapshot_dt.;
		%put &term_end_snapshot_dt.| " term_end " ;
	%end;

%else %if &snapshot2=eot %then 
	%do;
		%let snapshot_date2=&term_end_snapshot_dt.;
		%put &term_end_snapshot_dt.| " term_end " ;
	%end;

%else %if &snapshot2=begin %then 
	%do;
		%let snapshot_date2=&term_begin_dt.;
		%put &term_begin_dt.| " term_begin " ;
	%end;

 /* etc. */

%else 
	%do; /* default */
		%let snapshot_date2=&census_date.;
		%put &census_date.| " census " ;
	%end;
%put &snapshot_date2. &snapshot2;
%mend date_check;
%date_check;

I assume I would just change the macro to take one parm, the 'snapshot2' var, however after that I am not sure what to do. The dates examples are not that big of a deal for the formatting, any 4 or 5 'timedate22.' assigned to these vars will do.