topic Re: how to keep values with in macro in SAS Programming

How to create macro variables

thanikondharish — Wed, 24 Jul 2019 15:08:44 GMT

data test;
input zone $ state$ brnch$ 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
;

I have above dataset . How to create macro variales like see below examples..

ex1:

q1)how to create macro variable for zone distinct count

Ans: 2

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

Ans: zone1:SOUTH

ZONE2:NORTH

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 :DL

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

...........................................

how to keep in macro

Re: HOW TO CREATE MACRO VARIABLES

Tom — Wed, 24 Jul 2019 14:20:12 GMT

What did you try? What success did you have?

PS: Use the Insert Code button on the menu (look like {i}) when inserting monospaced text. That will keep the formatting and prevent the smileys.

Re: How to create macro variables

ballardw — Wed, 24 Jul 2019 15:33:00 GMT

how to keep in macro

What does that mean?

Re: How to create macro variables

Kurt_Bremser — Wed, 24 Jul 2019 16:54:07 GMT

Looks like homework you should be able to do on your own, with a little help from the documentation. Hint: look for call symput() and call symputx().

Re: How to create macro variables

ballardw — Wed, 24 Jul 2019 22:34:54 GMT

And Proc Freq with the OUT= tables option.

how to keep values with in macro

thanikondharish — Thu, 25 Jul 2019 13:48:26 GMT

q1)how to create macro variable for zone distinct count

Ans: 2

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

Ans: zone1: SOUTH

ZONE2: NORTH

i have tried below program for creationl of macro variables for zone ....

proc sql;
select count(distinct(zone)) into:zone_count from test ;
select distinct(zone)into:zone1-:zone%left(&zone_count) from test;
quit;

--------------------;

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 DL

i have tried below program for creationl of macro variables for state with zone ....

%macro loop;
%do i=1 %to &zone_count ;
%global &&zone&i%left(1);
proc sql;
select STATE,count(distinct(state)) into:&&zone&i%left(1) from test
where zone="&&zone&i";
quit;
%end;
%mend;

but the above program is not working....

please help how to create separate macro variables

Re: how to keep values with in macro

PaigeMiller — Thu, 25 Jul 2019 13:52:54 GMT

The phrase "keep values with in macro" does not have an obvious meaning to me.

The phrase "but the above program is not working...." requires a lot more information. What is not working? How do you know? Can you run the program again using OPTIONS MPRINT; as the first line, and then show us the LOG by pasting the log into the window that appears when you click on the {i} icon (do not paste the log directly into the reply window).

Re: how to keep values with in macro

Tom — Thu, 25 Jul 2019 13:53:53 GMT

What do you think the macro code you posted is doing?

What output do you expect?

Can you explain each step in the code?

Remember to use the Insert Code or Insert SAS Code buttons in forum editor so format is preserved.

%macro loop;
%do i=1 %to &zone_count ;
  %global &&zone&i%left(1);
proc sql;
  select STATE,count(distinct(state)) into:&&zone&i%left(1) from test
  where zone="&&zone&i";
quit;
%end;
%mend;

Re: how to keep values with in macro

Kurt_Bremser — Thu, 25 Jul 2019 15:42:19 GMT

I merged the thread into yesterday's thread with the same question.

Re: How to create macro variables

Tom — Thu, 25 Jul 2019 16:56:01 GMT

Start over from the questions and do this:

1) Show what you think the answer should be for each question if you just calculated the values manually.

Please include details about how you are resolving the ambiguity in the questions to reach those answers.

2) Then show how you attempted to generate those answers with code.

And indicate which ones either generated different answers than you expected or failed to generate any answer.

Re: how to keep values with in macro

hashman — Thu, 25 Jul 2019 21:36:19 GMT

@thanikondharish:

If your input data set, though not sorted, is grouped by (zone, state), as it appears to be in your data sample, there's no need to use SQL and reread it multiple times and/or use macros. Simply read it once and use BY processing with a nested DoW-loop:

data test ;                                              
  input (zone state brnch) ($) 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 ;                                                    
                                                         
data _null_ ;                                            
  do qs = 1 by 1 until (last.zone) ;                     
    do until (last.state) ;                              
      set test ;                                         
      by zone state notsorted ;                          
    end ;                                                
    call symputx (catx ("_", zone, "state", qs), state) ;
  end ;                                                  
  call symputx (catx ("_", "zone", _n_), zone) ;         
run ;                                                    
                                                         
%put _user_ ;

The %PUT statement reveals in the log:

GLOBAL ZONE_2 NORTH
GLOBAL ZONE_1 SOUTH
GLOBAL NORTH_STATE_1 MH
GLOBAL NORTH_STATE_2 GJ
GLOBAL NORTH_STATE_3 DL
GLOBAL SOUTH_STATE_1 AP
GLOBAL SOUTH_STATE_2 TS

If your real file is not grouped, sort it by (zone,state) beforehand.

Kind regards

Paul D.

Re: how to keep values with in macro

thanikondharish — Fri, 26 Jul 2019 01:00:43 GMT

This is code is fine .But how to find How many macro variables are created
state wise.

Re: how to keep values with in macro

hashman — Fri, 26 Jul 2019 01:11:25 GMT

@thanikondharish :

Not quite sure I understand your question. How this other macro variable you're talking about is supposed to be called? Which are the other macro variables whose count it should contain?

Re: how to keep values with in macro

thanikondharish — Fri, 26 Jul 2019 01:13:43 GMT

I need also how to keep distinct count values into macro variables

Re: how to keep values with in macro

hashman — Fri, 26 Jul 2019 02:12:08 GMT

@thanikondharish :

Again, you're making me guess. The code can be easily adapted to get any distinct counts within the existing sorted order. Assuming that what you need is the distinct count of (zone) and (zone,state) key combos:

data test ;                                              
  input (zone state brnch) ($) 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 ;                                                    
                                                         
data _null_ ;                                            
  do qs = 1 by 1 until (last.zone) ;                     
    do until (last.state) ;                              
      set test end = lr ;                                
      by zone state notsorted ;                          
    end ;                                                
    call symputx (catx ("_", zone, "state", qs), state) ;
    q + 1 ;                                              
  end ;                                                  
  call symputx (catx ("_", "zone", _n_), zone) ;         
  if lr ;                                                
  call symputx (catx ("_", "zone_count"), _n_) ;         
  call symputx (catx ("_", "zone_state_count"), q) ;     
run ;                                                    
                                                         
%put _user_ ;

And in the log, you'll get:

GLOBAL ZONE_COUNT 2
GLOBAL ZONE_2 NORTH
GLOBAL ZONE_1 SOUTH
GLOBAL NORTH_STATE_1 MH
GLOBAL NORTH_STATE_2 GJ
GLOBAL NORTH_STATE_3 DL
GLOBAL ZONE_STATE_COUNT 5
GLOBAL SOUTH_STATE_1 AP
GLOBAL SOUTH_STATE_2 TS

Kind regards

Paul D.

Re: How to create macro variables

ScottBass — Fri, 26 Jul 2019 06:34:54 GMT

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;

Re: How to create macro variables

ScottBass — Fri, 26 Jul 2019 06:36:46 GMT

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... ;