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
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
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;
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.
lol, "unlimited power"
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
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.
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.
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.
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.