Quartz | Level 8

## How to code a missing value in YTD data ?

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) ?

Regards,

WT196838

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: How to code a missing value in YTD data ?

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;

``````

2 REPLIES 2
Tourmaline | Level 20

## Re: How to code a missing value in YTD data ?

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;

``````

Quartz | Level 8

## Re: How to code a missing value in YTD data ?

Thanks for ChrisNz. Based on your codes, I developed my program and resolved my issue.

Best of all to sas user community.

Regards,

wtien196838

Discussion stats
• 2 replies
• 940 views
• 0 likes
• 2 in conversation