Macro to generate reports in SAS

0
votes

I have dataset in excel with varaible shop as spaces and & in the data. I want to do some kind of reports and charts based on the shop and also by family.But the client want to keep the shop name as it is. Ex: taba&wv, sup top. how can i add MACRO to generate tables by shop with this kind of names and how to generate reports like PROC REPORT,PROC TABULATE and CHARTS by shop using macro. Please find the sample data below

Shop    Year   Month      Family  Value1 Value2  Value3
raoas   2006   january    TA12     5      6       0
taba&wv 2008   january    TS01     0      1       1
sup top 2008   april      TZ05     0      0       1
taba&wv 2006   December   TA12     5      6       0
raoas   2008   january    TA15     0      2       0
sup top 2008   april      TQ05     0      1       1

The tables I want to create should looks like

taba&wv
Year Family sum_value1 Sumvalue2 Sum_value3

sup top 
Year Family sum_value1 Sumvalue2 Sum_value3

raoas  
Year Family sum_value1 Sumvalue2 Sum_value3
2
sas

2 Answers

2
votes

If you want just separate reports for different shops (so no need for naming datasets or variables with shop-names), then you'll be fine just masking properly shop titles. Here's, for example, creating PROC TABULATEs for each shop:

data have;
    infile datalines dsd dlm=',';
    input Shop $ Year Month $ Family $ Value1 Value2 Value3 ;
    datalines;
raoas,2006,january,TA12,5,6,0
taba&wv,2008,january,TS01,0,1,1
sup top,2008,april,TZ05,0,0,1
taba&wv,2006,December,TA12,5,6,0
raoas,2008,january,TA15,0,2,0
sup top,2008,april,TQ05,0,1,1
;run;

proc sql noprint;
    select distinct shop into :shops separated by '|'
    from have;
quit;

%macro reports;
    %do i=1 %to %sysfunc(countw(%superq(shops),|));
        title "PROC TABULATE for %qscan(%superq(shops),&i,|)";
        proc tabulate data=have(where=(shop="%qscan(%superq(shops),&i,|)"));
            class year month family;
            var value:;
            table   year*month*family
                    ,
                    (value1 value2 value3)*sum;
        run;
    %end;
%mend reports;
%reports

%SUPERQ() doesn't let macro compiler to try to resolve taba&wv as macrovariable, and %QSCAN() as well mask & and blanks while scanning the name.

NB: Macrovariable as argument in %SUPERQ()-function must be WITHOUT &.

1
votes

The & in the one name will create a problem. It is not allowed in a SAS name. Also the space will be a problem if not operating with VALIDVARNAME=ANY. I would convert both to _ at the beginning.

%macro create_tables(input,output);
data temp;
set &input;
Shop = translate(shop,'__','& ');
run;

%local n i;
proc sql noprint;
select count(distinct shop)
   into :n
   from temp;

%do i=1 %to &n;
   %local v&i;
%end;

select distinct shop
   into :v1 - :v%left(&n)
   from temp;

%do i=1 %to &n
create table &&v&i as
select shop, 
       Family,
       sum(Value1) as sum_value1,
       sum(Value2) as sum_value2,
       sum(Value3) as sum_value3
   from temp 
   where shop = "&&v&i"
   group by shop, family;
%end;
%quit;
%mend;

This uses PROC SQL to get the distinct shops into a series of macro variables &v1 - &vN. It then uses SQL to query the large table doing your sum of the values.