CODEWITHSUNDEEP
sqlsassas-macro
Azeem112
Azeem112

Reputation: 377

Overwriting/Appending sas variable

%let rows = "";

%macro test;

 proc sql noprint;
   select count(ID)
   into: sqlRows
   from mytbl;
 quit;

 %do i = 1 %to &sqlRows; * loop from 1 to sqlRows;

   proc sql noprint;
     select ID
     into: ColumnID
     from mytbl(firstobs= &i);
   quit;

   %if &rows eq "" %then %do
     %let rows = "<tr><td>&ColumnID</td></tr>";
   %end;

   %if &rows ne "" %then %do
     %let rows = "&rows<tr><td>&ColumnID</td></tr>";
   %end;

 %end;*End loop;

%mend;

%test;

%put &rows;

Hi I want to put all data of column ID data of mytbl into a variable.

I've created a variable named rows and assigned empty value in it. Then using loop I'm getting the values one by one of mytab and saving them in columnID variable. if rows variable is empty then only add tr and td with columnID data. if rows variable is not empty then append it. but it's only giving me the last record of my table.

lets say mytbl has data 1,2 and 3 in ID column

rows variable should have data as 

   <tr><td>1</td></tr><tr><td>2</td></tr><tr><td>3</td></tr>

but its only showing me data of last row as

<tr><td>3</td></tr>

Upvotes: 0

Views: 603

Answers (2)

Joe
Joe

Reputation: 63424

I think you're severely misunderstanding what macro variables are, as opposed to regular variables, in SAS. You don't say exactly what you're going to eventually do with this, but nonetheless.

First off, macro variables don't take quotation marks; if they contain them, they're treated just as regular characters. So:

%let var = "";
%let var = "&var.123";
%put &=var.;

will return

"""123"

since it doesn't really know much about the quotation marks (it is somewhat aware of them, but it doesn't treat them the way a normal SAS variable does).

Second, as Quentin correctly points out, why on earth are you using SQL to go a row at a time? That's basically the opposite reason as what you'd use SQL for. SQL is great for doing something to the whole dataset at once, it's absolutely horrible at one row at a time- that's what the data step is for.

If you actually want a SAS variable, or you want to process things a row at a time, you should just use the data step:

data want;
  set mytbl end=eof;
  retain rows;  *do not need to initialize to missing, that is normal;
  length rows $32767;
  rows = cats(rows,"<tr><td>",ColID,"</td></tr>");
  if eof then output;
run;

You'd usually do that if you were going to use call execute, for example if you planned to put this to an HTML page (in a stored proc for example) with some wrapper code that you wanted to execute, in if _n_=1 for the start and if eof for the end.

Upvotes: 3

Quentin
Quentin

Reputation: 6378

You've got a few different problems, starting with some missing semicolons. More importantly, your code is more complex than it needs to be. You can get what you want with one PROC SQL step using SELECT INTO:, you don't need a separate PROC SQL step for each record. Play around with:

data have;
  do ID=1 to 3;
    output;
  end;
run;

proc sql noprint;
  select cats('<tr><td>',ID,'</td></tr>') 
    into :Rows
    separated by ""
  from have;
quit;

%put &rows;

Upvotes: 4

Related Questions