DATA Step, Macro, Functions and more

How do I combine each row into one big row?

Reply
Frequent Contributor
Posts: 133

How do I combine each row into one big row?

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?

Super User
Posts: 10,044

How do I combine each row into one big row?

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

Frequent Contributor
Posts: 133

How do I combine each row into one big row?

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;

Super User
Posts: 10,044

How do I combine each row into one big row?

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

Super User
Posts: 5,516

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

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.

Frequent Contributor
Posts: 133

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

Posted in reply to Astounding

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?

Super User
Posts: 5,516

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

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.

Respected Advisor
Posts: 4,173

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

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

Super User
Posts: 11,343

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

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.

Frequent Contributor
Posts: 133

How do I combine each row into one big row?

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

Respected Advisor
Posts: 3,156

How do I combine each row into one big row?

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

Frequent Contributor
Posts: 133

How do I combine each row into one big row?

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

Respected Advisor
Posts: 3,156

How do I combine each row into one big row?

proc sql;

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

quit;

note:

added: distinct,

deleted: group by cat.

Super Contributor
Posts: 1,636

How do I combine each row into one big row?

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;

Frequent Contributor
Posts: 133

How do I combine each row into one big row?

Really really love the code!

Ask a Question
Discussion stats
  • 18 replies
  • 300 views
  • 8 likes
  • 7 in conversation