topic Re: How to filter date variable for a series of datasets? in SAS Programming

How to filter date variable for a series of datasets?

Shradha1 — Thu, 06 May 2021 14:39:44 GMT

I have datasets with their names indexed on date, say ABC_20210424, ABC_20210425, ABC_20210426 and so on.

Each dataset contains the same variables and one variable is Date. I have to use these datasets for a range of days, suppose from 15 APR 2021 to 30APR 2021 and within each dataset I want to select only those observations for which the Date variable takes value equal to the date indexed on the dataset name. For example, ABC_20210424 dataset has a range of observations with varying dates, but I want to select only those observations where Date=20210424. How do I achieve this? I am trying the following piece of code but its giving the error:

Code I am using:

data _null_;

call symputx('Start_d11', put(intnx('month', today(),0), yymmddn8.));

call symputx('SCORING_DATE' , put(intnx('day', today(), -1, 's'), yymmddn8.));

run;

data ABCD&Start_d1. - ABCD&SCORING_DATE1. (where("&Start_d11"d<=date<="&SCORING_DATE"d)) ;
set ABC&Start_d1. - ABC&SCORING_DATE1.;
run;

Please help!

Re: How to filter date variable for a series of datasets?

Kurt_Bremser — Thu, 06 May 2021 14:55:31 GMT

Use the INDSNAME= option of the SET statement:

data ABC_20210424;
input id $ date :yymmdd8.;
format date yymmdd10.;
datalines;
A 20210424
A 20210425
;

data ABC_20210425;
input id $ date :yymmdd8.;
format date yymmdd10.;
datalines;
B 20210424
B 20210425
;

data want;
length inds $41;
set
  ABC_20210424
  ABC_20210425
  indsname=inds
;
if date = input(scan(scan(inds,2,"."),2,"_"),yymmdd8.);
run;

Re: How to filter date variable for a series of datasets?

Reeza — Thu, 06 May 2021 16:12:11 GMT

Remove the formats from the macro variables so that SAS will understand the values to be dates
Apply the formats where needed using %SYFUNC() and PUTN()

  (where("&Start_d11"d<=date<="&SCORING_DATE"d))

Resolves to the following which is not valid SAS syntax. So assuming your dates are SAS dates, leave it as an unformatted date by removing the PUT() and displayed format.

  (where("20200506"d<=date<="20210506"d))

This should work instead, untested as no data etc..

data _null_;

call symputx('Start_d11', intnx('month', today(),0));

call symputx('SCORING_DATE' , intnx('day', today(), -1, 's'));

run;

 

data ABCD&Start_d1. - ABCD%sysfunc(putn(&SCORING_DATE1. , yymmddn8.)       (where(&Start_d11<=date<=SCORING_DATE)) ;
set ABC&Start_d1. - ABC%sysfunc(&SCORING_DATE1., yymmddn8.);
run;

@Shradha1 wrote:

I have datasets with their names indexed on date, say ABC_20210424, ABC_20210425, ABC_20210426 and so on.

Each dataset contains the same variables and one variable is Date. I have to use these datasets for a range of days, suppose from 15 APR 2021 to 30APR 2021 and within each dataset I want to select only those observations for which the Date variable takes value equal to the date indexed on the dataset name. For example, ABC_20210424 dataset has a range of observations with varying dates, but I want to select only those observations where Date=20210424. How do I achieve this? I am trying the following piece of code but its giving the error:

<LINE and COLUMN cannot be determined>

Code I am using:

data _null_;

call symputx('Start_d11', put(intnx('month', today(),0), yymmddn8.));

call symputx('SCORING_DATE' , put(intnx('day', today(), -1, 's'), yymmddn8.));

run;

data ABCD&Start_d1. - ABCD&SCORING_DATE1. (where("&Start_d11"d<=date<="&SCORING_DATE"d)) ;
set ABC&Start_d1. - ABC&SCORING_DATE1.;
run;

Please help!