- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content