How to create macro variables

[ScottBass]

q1)how to create macro variable for zone distinct count

Ans:   2

Check

q2)how to create macro variable for distinct values of zone

Ans:       zone1OUTH

               ZONE2:NORTH 

Check

q3)how to create macro variable for distinct count of state with in zone

Ans:      SOUTH_state_1:    AP

             SOUTH_state_2:    TS

             NORTH _state_1:    MH

             NORTH _state_2:    GJ

             NORTH _state_3    L

Unclear:  do you want count or values?

q3)how to create macro variable for distinct count of branch with in zone and state

Ans:      SOUTH_AP_branch1:  VZG

             SOUTH_AP_branch2:   TRP

              SOUTH_AP_branch3:  GUN

             SOUTH_state_TS_branch1:   HYD

             SOUTH_state_TS_branch2:   WGL

Unclear:  do you want count or values?

My "gut" says this is BAD...explain why you want to create these macro variables?  I bet storing in a data set would be a better approach.

Anyway, here you go.  Adjust if I misunderstood your unclear criteria:

* reset from previous runs ;
%global mvars;
proc sql noprint;
   select name into :mvars separated by " "
   from dictionary.macros
   where name like 'ZONE%' 
      or name like '%STATE%'
      or name like '%BRANCH%'
   order by name;
quit;

%symdel &mvars / nowarn;

data have;
input zone $ state$ branch$ sales;
cards;
SOUTH AP VZG 254
SOUTH AP TRP 247
SOUTH AP GUN 321
SOUTH TS HYD 147
SOUTH TS WGL 315
NORTH MH MUM 254
NORTH MH PUN 247
NORTH GJ AHM 323
NORTH DL NCR 148
NORTH DL NOD 319
;
run;

* sort ;
proc sort;
   by zone state branch;
run;

data _null_;
   set have end=eof;
   by zone state branch;
   if first.zone then do;
      zone_count+1;
      call symputx(catx("_","zone",zone_count),zone);
   end;
   if first.state then do;
      if first.zone then state_count=0;
      state_count+1;
      call symputx(catx("_",zone,"state",state_count),state);
   end;
   if first.branch then do;
      if first.zone or first.state then branch_count=0;
      branch_count+1;
      call symputx(catx("_",zone,state,"branch",branch_count),branch);
   end;
   if eof then call symputx("zone_count",zone_count);
run;

proc sql;
   select name, value
   from dictionary.macros
   where name like 'ZONE%' 
      or name like '%STATE%'
      or name like '%BRANCH%'
   order by name;
quit;

[ScottBass]

I was also playing with this, but naming your macro variables as you described is easier in a data step:

* for debugging, print to output window ;
proc sql;
   * zone distinct count ;
   select count(0) as zone_count 
   from (select distinct zone from test);

   * zone distinct values ;
   select distinct zone as zone_value 
   from test;

   * distinct count of state within zone ;
   select zone, state, count(0) as zone_state_count 
   from (select distinct zone, state from test) group by zone;

   * distinct value of state within zone ;
   select distinct zone, state as zone_state_value 
   from test;

   * distinct count of branch within within zone + state ;
   select zone, state, branch, count(0) as zone_state_branch_count 
   from (select distinct zone, state, branch from test) group by zone, state;

   * distinct value of branch within within zone + state ;
   select distinct zone, state, branch as zone_state_branch_value 
   from test;
quit; 

* I don't think you need macro variables, but if you REALLY want them ;
proc sql;
   * zone distinct count ;
   select count(0) as zone_count into :zone_count trimmed
   from (select distinct zone from test);
   %put &=zone_count;

   * zone distinct values ;
   select distinct zone as zone_value into :zone1 - :zone9999
   from test;
   %put &=zone1;
   %put &=zone2;

   * distinct count of state within zone ;
   * skipping this one... ;
   select zone, state, count(0) as zone_state_count 
   from (select distinct zone, state from test) group by zone;

   * distinct value of state within zone ;
   select distinct zone, state as zone_state_value 
   from test;

   * distinct count of branch within within zone + state ;
   select zone, state, branch, count(0) as zone_state_branch_count 
   from (select distinct zone, state, branch from test) group by zone, state;

   * distinct value of branch within within zone + state ;
   select distinct zone, state, branch as zone_state_branch_value 
   from test;

   * at this point I gave up... ;