Dear sas user community,
I develop codes to sum year to date data, the code as follows:
proc sql;
create table ytd_data as
select location,
qtr as period, /* qtr in Q1,Q2,Q3,Q4 */
sum(volume) as volume,
sum(var0) as var0
from (quarterly data)
group by location , qtr
UNION
select location,
YTD as period,
sum(volume) as volume,
sum(var0) as var0
from (quarterly data)
group by location
;
Quit;
As a result, I get the output :
location period volume var0
A Q1 10 5 (not exist this line if missing)
A Q2 10 8
A Q3 20 7
A Q4 50 9
A YTD 90 29
Then I transpose it to horizontal line :
Location YTD YTD_var0 Q1 Q1_var0 ...
A 90 29 10 5
Now if I have missing data on quarter (for example Q1) , I assign "." to it such as
Location YTD YTD_var0 Q1 Q1_var0
A 90 29 . . other quarters values
How can I code the missing quarter values for all locations (about 80) ?
I appreciate your support.
Regards,
WT196838
If you summarise using proc means instead of proc sql, you can load a CLASSDATA table to ensure all the crossings you want are created.
data HAVE;
length PERIOD $3;
retain VOLUME VAR0 1 ;
do LOCATION='A','B';
do i='01jan2016'd to '01sep2016'd;
PERIOD='Q'||put(I,qtr.);
output;
end;
end;
run;
data QTR;
length PERIOD $3;
do LOCATION='A','B';
do I=1 to 4;
PERIOD=cats('Q',I);
output;
end;
end;
run;
proc means data=HAVE classdata=QTR noprint;
class LOCATION PERIOD ;
types LOCATION LOCATION*PERIOD;
var VOLUME VAR0;
output out=SUM sum=;
run;
data SUM2;
set SUM(where=(_TYPE_=3))
SUM(where=(_TYPE_=2)) ;
by LOCATION ;
if _TYPE_=2 then PERIOD='YTD';
run;
If you summarise using proc means instead of proc sql, you can load a CLASSDATA table to ensure all the crossings you want are created.
data HAVE;
length PERIOD $3;
retain VOLUME VAR0 1 ;
do LOCATION='A','B';
do i='01jan2016'd to '01sep2016'd;
PERIOD='Q'||put(I,qtr.);
output;
end;
end;
run;
data QTR;
length PERIOD $3;
do LOCATION='A','B';
do I=1 to 4;
PERIOD=cats('Q',I);
output;
end;
end;
run;
proc means data=HAVE classdata=QTR noprint;
class LOCATION PERIOD ;
types LOCATION LOCATION*PERIOD;
var VOLUME VAR0;
output out=SUM sum=;
run;
data SUM2;
set SUM(where=(_TYPE_=3))
SUM(where=(_TYPE_=2)) ;
by LOCATION ;
if _TYPE_=2 then PERIOD='YTD';
run;
Thanks for ChrisNz. Based on your codes, I developed my program and resolved my issue.
Best of all to sas user community.
Regards,
wtien196838
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.