topic How do I combine each row into one big row? in SAS Programming

How do I combine each row into one big row?

ZRick — Thu, 08 Mar 2012 06:07:25 GMT

data t;

length stmt $50;

input stmt &$;

cards;

data tmp

set sashelp.car

run

;

data t2(keep = m);

set t;

m = trim (left(stmt)) || ";";

run;

I want to write a macro, a macro that merges all rows into one big row then pass it along to a variable, using %put to output it.

Here I give an example 3 rows, the number of rows could be dynamic,

How do I do that?

How do I combine each row into one big row?

Ksharp — Thu, 08 Mar 2012 06:24:41 GMT

I don't know how you define the number of rows could be dynamic?

But code might be like:

data t;
format stmt $50.;
input stmt $;
cards;
data tmp
set sashelp.car
run
;
run;
data want;
 set t end=last;
 length row $ 3200;
 retain row;
 row=catx(';',row,stmt);
 if last then output;
 keep row;
run;

Ksharp

How do I combine each row into one big row?

ZRick — Thu, 08 Mar 2012 06:42:48 GMT

I think I am getting there， Ksharp， your code is really useful, I edit it, but final output appeared twice, one shows up as "data tmp", one shows up as the right combination "data tmp; set sashelp.car; run", 2nd one is what I want, not "data tmp", any ideas?

data t;

length stmt $50;

input stmt &$;

cards;

data tmp

set sashelp.car

run;

data _null_;

set t end=last;

length row $ 3200;

retain row;

row=catx("; ",row,stmt);

if last then call symput('var', row);

run;

%put &var;

How do I combine each row into one big row?

Ksharp — Thu, 08 Mar 2012 07:17:33 GMT

It is very interesting. Obviously SAS take the value be a sas statement. We should mask them all.

data t;
length stmt $50;
input stmt &$;
cards;
data tmp
set sashelp.car
;
run;

data _null_;
set t end=last;
length row $ 3200;
retain row;
row=catx("; ",row,stmt);
if last then call symput('var', row);
run;

%put %bquote(&v) ;

Ksharp

Re: How do I combine each row into one big row?

Astounding — Thu, 08 Mar 2012 13:55:43 GMT

Here's a neat way to combine them. You still might face the issues you saw when printing with %PUT (although it's not clear why in the world you would want to use %PUT to output the results):

proc sql;

select trim(stmt) into : macrovar separated by ';' from t;

quit;

You would still need to add one final semicolon at the end.

Good luck.

Re: How do I combine each row into one big row?

ZRick — Thu, 08 Mar 2012 16:25:09 GMT

You asked about "why you want to use %put to output the results"

Basically, you see, entrie row will be big data step, I want to use them in the macro, so when this macro is called, the data step is executed automatically.

I don't know if output to log is the right place to do that, maybe you have a better suggestion?

Re: How do I combine each row into one big row?

Astounding — Thu, 08 Mar 2012 18:24:14 GMT

Well, if you have a macro variable holding all the statements that should execute, just add:

&macrovar;

I added the semicolon at the end to allow for my variation where the final semicolon had to be added.

You might play with these options first to find a combination that would give you just one version of the statements on the log:

options nosymbolgen;

options nomprint;

And if you don't need to "save up" the statements but can run them immediately, you could skip creating a macro variable entirely. Here's a variation on your original DATA step:

data _null_;

length stmt $ 50;

input stmt $ &;

call execute (stmt || ';');

cards;

...

This approach has a theoretical drawback. If the incoming lines contain any macro language commands (rather than DATA step statements), they will execute immediately (out of order) before the DATA step statements execute.

Good luck.

Re: How do I combine each row into one big row?

Patrick — Thu, 08 Mar 2012 18:52:24 GMT

I find it normally easier to write dynamically created code to a temporary file. From what you describe I believe this approach should also work for what you want to do.

filename code temp;

data _null_;

file code;

input;

put _infile_;

datalines4;

data tmp;

set sashelp.class;

run;

proc print data=tmp;

run;

;;;;

run;

%macro test(check);

%if &check=1 %then

%include code;

%else %put Nothing to execute;

%mend;

/*%test(1);*/

%test(0);

Re: How do I combine each row into one big row?

ballardw — Thu, 08 Mar 2012 19:11:28 GMT

I think Patrick is pointing you in a better direction.

You may run into problems with the size a single macro variable will be with this approach as well.

I had at one time responsibility for creating code from a bunch of descriptors. The total created over 50 SAS program files and about 6 megabytes of code. Trying to maintain that with a single macro variable would nave been unfeasible.

Also you're likely to have issues with macro quoting if you include any macro variables, definitions or calls within the generated code.

How do I combine each row into one big row?

ZRick — Fri, 09 Mar 2012 00:10:42 GMT

How do I lump them into 2 rows now by cat?

for following code, lump them into two rows, one for cat=1, one for cat=2

data tmp;
length stmt $50;
input cat stmt &$;
datalines4;
1 data A;
1 set sashelp.class;
1 run;
1 proc print data=a;
1 run;
2 data B;
2 set sashelp.cars;
2 run;
2 proc print data=b;
2 run;
;;;;

proc sql;
select trim(stmt) into : macrovar separated by '' from tmp;
quit;

%put %bquote(&macrovar);

How do I combine each row into one big row?

Haikuo — Fri, 09 Mar 2012 00:55:33 GMT

Not sure what do you mean by 'two rows'. is this what you want?

proc sql;

select trim(stmt) into : macrovar1 separated by '' from tmp where cat=1;

select trim(stmt) into : macrovar2 separated by '' from tmp where cat=2;

quit;

%put %bquote(&macrovar1);

%put %bquote(&macrovar2);

Regards,

Haikuo

How do I combine each row into one big row?

Linlin — Fri, 09 Mar 2012 00:55:50 GMT

how about:

data tmp;

length stmt $50;

input cat stmt &$;

datalines4;

1 data A;

1 set sashelp.class;

1 run;

1 proc print data=a;

1 run;

2 data B;

2 set sashelp.cars;

2 run;

2 proc print data=b;

2 run;

;;;;

data want (keep=_stmt);

length _stmt $ 100;

set tmp;

by cat;

if first.cat then _stmt='';

retain _stmt;

_stmt=catx('',_stmt,stmt);

if last.cat then output;

run;

proc print;run;

Obs _stmt

1 data A; set sashelp.class; run; proc print data=a; run;

2 data B; set sashelp.cars; run; proc print data=b; run;

How do I combine each row into one big row?

Ksharp — Fri, 09 Mar 2012 01:57:42 GMT

It looks like you are creating dynamic code.

As my experience, CALL EXECUTE is the best choice.

Ksharp

How do I combine each row into one big row?

ZRick — Fri, 09 Mar 2012 02:15:20 GMT

Really really love the code!

How do I combine each row into one big row?

ZRick — Fri, 09 Mar 2012 02:15:50 GMT

would you mind illustrating little bit?

How do I combine each row into one big row?

ZRick — Fri, 09 Mar 2012 02:17:49 GMT

it is ok to use "Cat =1", "cat =2",

but I prefer not to, because that is "hardcoding",

if the cat is dynamiclly generated, I have no way of knowing cat=??.

How do I combine each row into one big row?

Haikuo — Fri, 09 Mar 2012 02:26:15 GMT

proc sql;

select count(distinct cat) into :mv from tmp;

quit;

note:

added: distinct,

deleted: group by cat.

How do I combine each row into one big row?

Ksharp — Fri, 09 Mar 2012 05:57:56 GMT

For the Linlin's example .

data temp;
input tname $40. ;
cards;
sashelp.class
sashelp.cars
;
run;
data _null_;
 set temp;
 call execute(catx(' ','data',scan(tname,-1,'.'),'; set',tname,';run; proc print;run;'));
run;

Ksharp

How do I combine each row into one big row?

Astounding — Fri, 09 Mar 2012 13:26:26 GMT

ZRick,

I can't help but wonder if we're approaching this problem the right way. There's an earlier step that generates all these lines of SAS code. Now we're looking to reorganize them and run them. Would it make sense to break into the earlier step instead? Right now it generates several streams of SAS statements. Would it make more sense for the earlier step to generate stream1.sas with all the cat=1 lines, stream2.sas with all the cat=2 lines, etc.? You would have ready-to-run programs holding all the proper statements.

Just a thought.