How to code a missing value in YTD data ?

[wtien196838]

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

[ChrisNZ · Accepted solution]

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;

    

[wtien196838]

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

Best of all to sas user community.

Regards,

wtien196838