topic Re: Multiple Date formats in a single Text string in SAS Programming

Multiple Date formats in a single Text string

Rebecca_K — Wed, 06 Nov 2024 14:16:23 GMT

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!

Re: Multiple Date formats in a single Text string

PaigeMiller — Wed, 06 Nov 2024 15:00:00 GMT

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

Re: Multiple Date formats in a single Text string

Quentin — Wed, 06 Nov 2024 15:34:55 GMT

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.

Re: Multiple Date formats in a single Text string

PaigeMiller — Wed, 06 Nov 2024 15:39:44 GMT

@Quentin wrote:

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

Yes, excellent point by @Quentin