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
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.