proc expand and keep some variables

mala6861 — Mon, 03 Sep 2018 00:01:18 GMT

Hi everyone,

I have a quarterly dataset named ab with variables DATADATE,LPERMNO,DLCQ, DLTTQ, CIK, CUSIP

I want to convert the quarterly dataset ab into monthly, but only covert “DLCQ DLTTQ”, and I want to keep the other variables CIK, CUSIP

I can use this code,

proc expand data=ab out=abc from=qtr to=month;

convert DLCQ DLTTQ;

id DATADATE;

by LPERMNO;

run;

The new data set 'abc' only keeps,

DATADATE, LPERMNO, DLCQ, DLTTQ

For example,

DATA AB

LPERMNO	DATADATE	DLCQ	DLTT	CUSIP	CIK
111	Jan-90	111.1	5422.4	622	abc
111	Apr-90	112.3	5504.7	622	abc
111	Jul-90	113.6	5570.5	622	abc
111	Oct-90	114.5	5557.5	622	abc
113	Jan-90	115.9	5589	988	mkk
113	Apr-90	116.8	5652.6	988	mkk
113	Jul-90	117.4	5709.2	988	mkk
113	Oct-90	.	5736.6	988	mkk

after using this code

proc expand data=ab out=abc from=qtr to=month;

convert DLCQ DLTTQ;

id DATADATE;

by LPERMNO;

run;

I get the dataset abc

LPERMNO	DATADATE	DLCQ	DLTT
111	Jan-90	110.879	5409.69
111	Feb-90	111.048	5417.67
111	Mar-90	111.367	5439.39
111	Apr-90	111.802	5470.58
111	May-90	112.297	5505.35
111	Jun-90	112.801	5538.14
111	Jul-90	113.264	5563.38
111	Aug-90	113.641	5575.69
111	Sep-90	113.905	5572.49
111	Oct-90	114.139	5561.64
111	Nov-90	114.451	5553.83
111	Dec-90	114.909	5556.92
113	Jan-91	115.452	5570.06
113	Feb-91	115.937	5588.18
113	Mar-91	116.314	5608.68
113	Apr-91	116.6	5630.81
113	May-91	116.812	5652.92
113	Jun-91	116.988	5674.06
113	Jul-91	117.164	5693.43
113	Aug-91	117.38	5710.54
113	Sep-91	117.665	5724.11
113	Oct-91	.	5733.65
113	Nov-91	.	5738.46
113	Dec-91	.	5737.75

However, I want to keep the CUSIP and CIK as well, with out converting (not like DLCQ and DLTT), just like LPREMNO, so my data set should look like below

LPERMNO	DATADATE	DLCQ	DLTT	CUSIP	CIK
111	Jan-90	110.879	5409.69	622	abc
111	Feb-90	111.048	5417.67	622	abc
111	Mar-90	111.367	5439.39	622	abc
111	Apr-90	111.802	5470.58	622	abc
111	May-90	112.297	5505.35	622	abc
111	Jun-90	112.801	5538.14	622	abc
111	Jul-90	113.264	5563.38	622	abc
111	Aug-90	113.641	5575.69	622	abc
111	Sep-90	113.905	5572.49	622	abc
111	Oct-90	114.139	5561.64	622	abc
111	Nov-90	114.451	5553.83	622	abc
111	Dec-90	114.909	5556.92	622	abc
113	Jan-91	115.452	5570.06	988	mkk
113	Feb-91	115.937	5588.18	988	mkk
113	Mar-91	116.314	5608.68	988	mkk
113	Apr-91	116.6	5630.81	988	mkk
113	May-91	116.812	5652.92	988	mkk
113	Jun-91	116.988	5674.06	988	mkk
113	Jul-91	117.164	5693.43	988	mkk
113	Aug-91	117.38	5710.54	988	mkk
113	Sep-91	117.665	5724.11	988	mkk
113	Oct-91	.	5733.65	988	mkk
113	Nov-91	.	5738.46	988	mkk
113	Dec-91	.	5737.75	988	mkk

Thanks for the response

Re: proc expand and keep some variables

Jagadishkatam — Mon, 03 Sep 2018 01:09:10 GMT

Did you try the below approach.

proc sort data=ab;
by LPERMNO CUSIP CIK;
run;

proc expand data=ab out=abc from=qtr to=month;
   convert DLCQ DLTTQ;
   id DATADATE;
   by LPERMNO CUSIP CIK;
run;

Re: proc expand and keep some variables

PGStats — Mon, 03 Sep 2018 03:11:00 GMT

If many values of CUSIP may exist for some LPERMNO then you could convert them with the step method:

proc expand data=ab out=abc from=qtr to=month;
   convert DLCQ DLTT;
   convert CUSIP / method=step;
   id DATADATE;
   by LPERMNO CIK notsorted;
run;

Note that this method and the version proposed by @Jagadishkatam will fail if there are many values of CIK for some LPERMNO. It will not expand and interpolate between observations with different CIK values.

topic proc expand and keep some variables in SAS Programming

proc expand and keep some variables

Re: proc expand and keep some variables

Re: proc expand and keep some variables