topic Re: How to code a missing value in YTD data ? in SAS Programming

How to code a missing value in YTD data ?

wtien196838 — Thu, 30 Jun 2016 23:49:08 GMT

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

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

ChrisNZ — Fri, 01 Jul 2016 02:05:49 GMT

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;

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

wtien196838 — Wed, 06 Jul 2016 12:48:05 GMT

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

Best of all to sas user community.

Regards,

wtien196838