topic Re: SAS reading data in SAS Programming

SAS reading data

Test_yes — Wed, 16 Mar 2016 19:43:21 GMT

I have 2 variables and 3 records in a sas data set, and based on the date field in that data set, I need to read different monthly data sets. For example, I have item no. Date 1 30Jun2015 2 31Jul2015 3 31Aug2015 When I read the first record, then based on the date field (30jun2015) here, it should merge another dataset suffixed with 30jun2015 with this current dataset. How can I achieve that?

Re: SAS reading data

Reeza — Wed, 16 Mar 2016 19:47:13 GMT

Please post sample data and expected output.

Re: SAS reading data

Test_yes — Wed, 16 Mar 2016 20:44:44 GMT

Input dataset1:
item no. Date
1 30Jun2015
2 31Jul2015
3 31Aug2015
Input dataset2:(test_30june2015)
item no phone no
1 1111111111
4
5
Input dataset3:(test_31jul2015)
item no phone no
2 2222222222
7
 
Expected output:
item no. Date phone no
1 30Jun2015 1111111111
2 31Jul2015 2222222222
3 31Aug2015

Re: SAS reading data

LinusH — Wed, 16 Mar 2016 19:53:34 GMT

One way could be to use call execute in a data step that reads the control table. The call execute could call a macro that does the merging.

Re: SAS reading data

FreelanceReinh — Wed, 16 Mar 2016 20:58:05 GMT

Alternatively, the following approach might work (depending on your real data):

/* Create test data */

data have1;
input item date :date9.;
format date date9.;
cards;
1 30Jun2015
2 31Jul2015
3 31Aug2015
;

data test_30jun2015; /* changed from test_30june2015! */
input item phone;
cards;
1 1111111111
4 .
5 .
;

data test_31jul2015;
input item phone;
cards;
2 2222222222
7 .
; 

/* Select datasets and merge them with HAVE1 */

proc sql noprint;
select 'test_'||put(date,date9.) as dsname into :dslist separated by ' '
from have1
where exist(calculated dsname, 'DATA');
quit;

data want;
merge have1(in=a) &dslist;
by item;
if a;
run;

With your sample data, item no. could be used as well to select observations (and the file name test_30june2015 could be left unchanged):

proc sql noprint;
select item into :itemlist separated by ' '
from have1;
quit;

data want;
merge have1 test_:;
by item;
where item in (&itemlist);
run;

(But this might not be applicable to your real data.)

In any case the above merge steps would create unpleasant INFO messages in the log (if option MSGLEVEL=I):

INFO: The variable phone on data set WORK.TEST_30JUN2015 will be overwritten by data set WORK.TEST_31JUL2015.

Re: SAS reading data

Test_yes — Wed, 16 Mar 2016 21:17:49 GMT

Thanks. But how can I change if the date value in the 'have1' datset is like ' 30Jun2015' and the merging dataset as 'test_201506'.

Re: SAS reading data

FreelanceReinh — Wed, 16 Mar 2016 21:27:10 GMT

The following modification could work with character dates and dataset names of the form test_YYYYMM:

/* Create test data */

data have1;
input item date :$9.;
cards;
1 30Jun2015
2 31Jul2015
3 31Aug2015
;

data test_201506;
input item phone;
cards;
1 1111111111
4 .
5 .
;

data test_201507;
input item phone;
cards;
2 2222222222
7 .
; 

/* Select datasets and merge them with HAVE1 */

proc sql noprint;
select 'test_'||compress(put(input(date,date9.),yymm.),'M') as dsname into :dslist separated by ' '
from have1
where exist(calculated dsname, 'DATA');
quit;

data want;
merge have1(in=a) &dslist;
by item;
if a;
run;

Re: SAS reading data

Test_yes — Wed, 16 Mar 2016 21:39:21 GMT

Thanks. It is working now. And one last thing. how we can treat if the date field is like 2014-08-18 and we need to do the same thing.

Re: SAS reading data

FreelanceReinh — Wed, 16 Mar 2016 21:42:49 GMT

In this case, simply replace the informat date9. (in the SELECT statement) by yymmdd10..

Re: SAS reading data

Ksharp — Thu, 17 Mar 2016 01:46:09 GMT

The point is the MERGE BY variale is item and date or just item ?

data have1;
input item date :date9.;
format date date9.;
cards;
1 30Jun2015
2 31Jul2015
3 31Aug2015
;

data test_30jun2015; /* changed from test_30june2015! */
input item phone;
cards;
1 1111111111
4 .
5 .
;

data test_31jul2015;
input item phone;
cards;
2 2222222222
7 .
; 
data test(index=(item date));
 length dsname $ 40;
 set test_: indsname=dsname;
 date=input(scan(dsname,-1,'_'),date9.);
 format date date9.;
run;

data want;
 merge have1(in=ina) test;
 by item date;
 if ina;
run;