BookmarkSubscribeRSS Feed
kjohnsonm
Lapis Lazuli | Level 10

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

 

7 REPLIES 7
kjohnsonm
Lapis Lazuli | Level 10

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


ChrisNZ
Tourmaline | Level 20

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

kjohnsonm
Lapis Lazuli | Level 10

lol,    "unlimited power"

 

kjohnsonm
Lapis Lazuli | Level 10

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

ballardw
Super User

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.

kjohnsonm
Lapis Lazuli | Level 10

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.

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
  • 7 replies
  • 962 views
  • 0 likes
  • 4 in conversation