Converting a dataset with Proc Expand

[mala6861]

Hi, 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 without conversion 

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,

Thanks for the response

[PeterClemmensen]

So you want the variables CIK, CUSIP to be in the out= data set as well? But not with a monthly freq, correct?

[mala6861]

I want it in monthly frequency but without conversion, because they are identifiers

[mala6861]

Dear draycut,

So, yes I want the variables CIK, CUSIP to be in the out= data set as well.

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

[somebody]

Hi @mala6861

I run into your post which might be useful to me in the future. I have a quick question regarding your sample output. The values for DLCQ and DLTT are different between dataset abc and ab. For example, with LPERMNO = 111 and datate = Jan 90, DLCQ = 111.1 and DLTT=5422.4 in ab but they are 110.879 and 5409.69 in abc, and so on. Is there any reason for these differences? Shouldn't the values for Jan-90, Apr-90, Jul-90 ... be the same in both datasets?

[dw_sas]

Hi @somebody,

Thanks for your question.  When I run the sample input data and the solution code provided, the values for Jan-90, Apr-90, Jul-90, etc.,  in the monthly output data set, abc, are the same as the original quarterly values for those dates in the input data set, ab.  Please see the full example code below for illustration.

data ab;
  input lpermno date: monyy5. dlcq dltt cusip cik $;
  format date date9.;
  datalines;
111 jan90 111.1 5422.4 622 abc
111 apr90 112.3 5504.7 622 abc
111 jul90 113.6 5570.5 622 abc
111 oct90 114.5 5557.5 622 abc
113 jan90 115.9 5589 988 mkk
113 apr90 116.8 5652.6 988 mkk
113 jul90 117.4 5709.2 988 mkk
113 oct90 . 5736.6 988 mkk
;

proc print data=ab;
run;

proc sort data=ab;
  by cik date;
run;

proc expand data=ab out=abcd from=qtr to=month;
  by cik;
  id date;
  convert dlcq dltt lpermno cusip;
run;

proc print data=abcd;
  var lpermno date dlcq dltt cusip cik;
run;

I hope this helps!

DW

[dw_sas · Accepted solution]

Hi,

Based on your sample data, CUSIP and CIK are both constant within each level of your BY variable, LPERMNO.  Assuming LPERMNO and CUSIP are both numeric variables, you can obtain your desired output by using CIK as your BY variable instead of LPERMNO and adding LPERMNO and CUSIP to your CONVERT statement.  For example:

proc sort data=ab;
  by cik datadate;
run;

proc expand data=ab out=abcd from=qtr to=month;
  by cik;
  id datadate;
  convert dlcq dltt lpermno cusip;
run;

proc print data=abcd;
  var lpermno datadate dlcq dltt cusip cik;
run;

I hope this helps!

DW