topic Re: Creating a macro variable for different columns in SAS Programming

Creating a macro variable for different columns

sanjay1 — Fri, 09 Sep 2016 09:24:36 GMT

Hi Sas Experts,

Is there is anyway to create a single macrovariable for different columns. My intension is I want to create a macrovariable for all the products.

I mean the macro variable should populate all the products for each id. Please help.

Below is my code;

data sample;
infile datalines missover;
input id sales product1$ product2$ product3$ product4$;
datalines;
101 10 sofa
102 20 bed chair
103 30 mouse
104 49 desk phone bottle watch
run;

Re: Creating a macro variable for different columns

Reeza — Fri, 09 Sep 2016 09:41:02 GMT

What do you want as an output and why do you think you need a macro variable.

Re: Creating a macro variable for different columns

sanjay1 — Fri, 09 Sep 2016 10:11:34 GMT

Hi Reeza,

I have a large data set and I want to claculate the sales for each ID and theire corresponding products.

again am sending this report to different people using sas.

Thanks

Re: Creating a macro variable for different columns

Reeza — Fri, 09 Sep 2016 10:19:25 GMT

@sanjay1 wrote:

Hi Reeza,

I have a large data set and I want to claculate the sales for each ID and theire corresponding products.

again am sending this report to different people using sas.

Thanks

That doesn't explain what you want as output or why you need a macro variable. Or even what values you want in a macro value.

I could guess and say most likely all you need is a transpose so you can sum your report in various methods. But that's a guess.

Re: Creating a macro variable for different columns

sanjay1 — Fri, 09 Sep 2016 14:48:59 GMT

Thanks Reeza,

That helps me.

Re: Creating a macro variable for different columns

ballardw — Fri, 09 Sep 2016 15:00:07 GMT

I think you are looking for report. Something like this:

proc tabulate data=have;

class ID product;

var sales;

table Id, /* this produces one "page" for each ID*/

product, /* one row for each product*/

Sales *(n='Number of Sales' Sum='Total of Sales')

;

run;

Re: Creating a macro variable for different columns

Michiel — Fri, 09 Sep 2016 16:29:56 GMT

Add distinct column product1 into macro variables.

%MACRO sample;

proc sql noprint;
select distinct
product1 into :var1-:var9999
from sample
;
%LET nItems=&SQLOBS.;
%PUT nItems=&nItems.;
quit;

%DO Item=1 %TO &nItems.;

%PUT var&Item.=&&var&Item..;
%END;

%MEND;

%sample;