BookmarkSubscribeRSS Feed
ZRick
Obsidian | Level 7

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?

18 REPLIES 18
Ksharp
Super User

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
Obsidian | Level 7

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
Super User

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
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Opal | Level 21

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
Super User

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
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7

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
Onyx | Level 15

proc sql;

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

quit;

note:

added: distinct,

deleted: group by cat.

Linlin
Lapis Lazuli | Level 10

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
Obsidian | Level 7

Really really love the code!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 1396 views
  • 8 likes
  • 7 in conversation