How to create macro variables

[thanikondharish]

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

[Tom]

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. 

[ballardw]

---

how to keep in macro

---

What does that mean?

[Kurt_Bremser]

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().

[ballardw]

And Proc Freq with the OUT= tables option.

[thanikondharish]

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
;

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

[PaigeMiller]

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

[Tom]

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;

[Kurt_Bremser]

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

[hashman]

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

[thanikondharish]

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

[hashman]

@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?  

[thanikondharish]

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

[hashman]

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

[Tom]

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.