SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
Rebecca_K
Fluorite | Level 6

Hello!  Still pretty new to SAS.  I have a table of Check Dates that I need to compare my dataset to.  The trick is, sometimes the dates refer to a hard coded Macro variable date, and sometimes they come in as MM/DD/YYYY.  Was wondering if there is a magic wand to convert all dates to a single date format so i can do my compare.  Hope that makes sense!  Here's what I have.  Everything works except the final proc sql's where, which is where I'm stuck.  It's also probably not the most elegant solution, but it's where I'm at right now. 

DATA HAVE;
INPUT MEMBERID : $20. DOB : MMDDYY10.;
Format DOB MMDDYY10.;
CARDS;
1111 1/12/2001
2222 2/11/2003
3333 3/11/2003
4444 1/13/2000
;run;

DATA RANGE_CHECK;
INPUT Check_Number Low_DT : $50. High_DT : $50.;
CARDS;
1 3/1/2000 5/1/2001
2 &Hardcode_High_DT. &Hardcode_High_DT.
;run;


DATA FINAL_TABLE;
	format Check_Number 8.;
	format MEMBERID		$20.;
	format DOB 		 	MMDDYY10.;
;run;

%macro FindRanges();

 %let Hardcode_Low_DT = 02JAN2002;
 %let Hardcode_High_DT = 02DEC2003;


    proc sql  ;
           SELECT  Check_Number, 
                   quote(strip(resolve(Low_DT))), 
                   quote(strip(resolve(High_DT))) into 
			          :Check_Number_List separated by ' ',
			          :Low_DT_List separated by ';',
				      :High_DT_List separated by ';'
    		    FROM RANGE_CHECK;
				%let Check_Count =&sqlobs;
    ;quit;


  %do t = 1 %to &Check_Count.;

     %let Check_Number_Value = %scan(&Check_Number_List, &t);
     %let Low_Value = %scan(&Low_DT_List, &t, ';');   /* <- Probably Need to change this */
     %let High_Value = %scan(&High_DT_List, &t, ';'); /* <- Probably Need to change this */

    proc sql;
	     INSERT into FINAL_TABLE
         SELECT &Check_Number_Value., MEMBERID, DOB 
         From HAVE
		 Where DOB between &LowValue. and &High_Value.   /* <- DEFINITLY Need to change this! */

	 ;quit;
  
	 %end;


%mend FindRanges;

%FindRanges();

 What i'm hoping to have in the FINAL_TABLE would be:

FINAL_TABLE
Check_Number MEMBERID  DOB
1     	     1111 	1/12/2001
2	     2222       2/11/2003
2	     3333       3/11/2003

Thanks so much!

3 REPLIES 3
PaigeMiller
Diamond | Level 26

If you have hard coded macro variables that you want to work with, they cannot be in the CARDS part of a data step. Nevertheless, you can work with them in other ways. So range_check will have one observation, and you want to read the dates with Informat MMDDYY10.

 

 

 %let Hardcode_Low_DT = %sysevalf('02JAN2002'd);
 %let Hardcode_High_DT = %sysevalf('02DEC2003'd);

 

 

This turns the 02JAN2002 and 02DEC2003 into actual date values which SAS can work with. Your first SQL is unnecessary. You can turn the first row of RANGE_CHECK into macro variables with CALL SYMPUTX

 

data _null_;
    set range_check(obs=1);
    call symputx('low_dt',low_dt);
    call symputx('high_dt',high_dt);
run;

 

then in your second SQL you can do something like this

 

Where DOB between &low_dt and &high_dt

 

and/or

 

where dob between &Hardcode_Low_DT and &hardcode_high_dt

 

--
Paige Miller
Quentin
Super User

I would back up and think about non-macro approaches to implementing this sort of range check.

 

The idea of having a dataset range checks is a good one.  When you have date values in a dataset, you want to store them is date values (numeric variables), not character.  So your data could be like:

DATA HAVE;
INPUT MEMBERID : $20. DOB : MMDDYY10.;
Format DOB MMDDYY10.;
CARDS;
1111 1/12/2001
2222 2/11/2003
3333 3/11/2003
4444 1/13/2000
;run;

DATA RANGE_CHECK;
INPUT Check_Number Low_DT : mmddyy10. High_DT : mmddyy10.;
format Low_DT High_Dt mmddyy10. ;
CARDS;
1 3/1/2000 5/1/2001
2 1/2/2002 12/2/2003
;run;


Note I removed the macro variables from the CARDS data.  That won't work. If you want to make this data dynamic, happy to discuss alternative approaches.  I also make Low_DT and High_DT numeric date variables, rather than character.

 

Once you have that data, there are lots of different way to implement the range check.  One would be to use PROC SQL.  Below will join every record in your data against every check, and output the records where DOB is in the range.  You'll get a note in your log that SAS is performing a Cartesian product.  In this case, four records from HAVE * 2 records in RANGE_CHECK would result in 8 records, which are then filtered.  If you have big data, the Cartesian product may be slow, but it's one way you could approach the problem.

 

proc sql ;
  create table want as
  select b.check_number,a.memberid,a.dob
  from have as a
      ,range_check as b
  where b.low_dt<=a.dob<=b.high_dt
  ;
quit ;

 

If you look on lexjansen.com (an archive of SAS papers written by users) and search for "range check", you'll find lots of different approaches to implementing checks like this.

 

The Boston Area SAS Users Group is hosting free webinars!
Next up: Troy Martin Hughes presents Calling Open-Source Python Functions within SAS PROC FCMP: A Google Maps API Geocoding Adventure on Wednesday April 23.
Register now at https://www.basug.org/events.
PaigeMiller
Diamond | Level 26

@Quentin wrote:

I would back up and think about non-macro approaches to implementing this sort of range check.


Yes, excellent point by @Quentin 

--
Paige Miller

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 826 views
  • 2 likes
  • 3 in conversation