How do I combine each row into one big row?

[ZRick]

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?

[Ksharp]

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

[ZRick]

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;

[Ksharp]

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

[Astounding]

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.

[ZRick]

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?

[Astounding]

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.

[Patrick]

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

[ballardw]

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.

[ZRick]

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

[Haikuo]

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

[ZRick]

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=??.

[Haikuo]

proc sql;

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

quit;

note:

added: distinct,

deleted: group by cat.

[Linlin]

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;

[ZRick]

Really really love the code!