The SAS Output Delivery System and reporting techniques

branch wise excel file using macro

Reply
Contributor
Posts: 68

branch wise excel file using macro

Hi

I have huge data of mulitiple location, I want to create branch code  wise data set with same name as branch code  and excel file using marco.Below attcahed is sample data.

Branch_codecust_nameRevenue
100a25000
100s35000
100d45000
200f6000
200f5000
100b6000
300a6000
200d5000
200f6000
300g6000
Frequent Contributor
Posts: 138

Re: branch wise excel file using macro

 

%macro abc;

data test;

branch_code=100;

cust_name='a';

revenue=25000;

output;

branch_code=200;

cust_name='b';

revenue=2500;

output;

branch_code=200;

cust_name='c';

revenue=25000;

output;

branch_code=300;

cust_name='d';

revenue=5000;

output;

run;

proc sql;

select count(distinct(branch_code)) into :n from test;quit;

%let n= %trim(&n);

proc sql;

select distinct(branch_code) into :branch_code1-:branch_code&n from test;

quit;

%do i=1 %to &n;

data branch_&&branch_code&i. ;

set test;

if branch_code="&&branch_code&i." ;

run;

%end;

%mend;

%abc

Since dataset name cannot start with number i have prefixed with brnach

Contributor
Posts: 68

Re: branch wise excel file using macro

Thanx Manoj

it worked perfectly. request you t pls explain the same code

Frequent Contributor
Posts: 138

Re: branch wise excel file using macro

I see you are in gurgaon. You can reach me @9560771148 and i would be happy to explain further.

I have created a macro variable abc which uses the dataset test having the information of brnach_code etc.

Now i have created a macro variable n which contains total number of distinct brnach code.

Now the do loop runs the code for n number of times for each brnanch code.


Post a Question
Discussion Stats
  • 3 replies
  • 297 views
  • 0 likes
  • 2 in conversation