CODEWITHSUNDEEP
sassas-macro
Gaurav Jindal
Gaurav Jindal

Reputation: 3

Scan values from a macro variable and store it in a column in data set using SAS

%let abc = ("234.34", "C56.67", "2345.67", "C67.56") ;

Output: Table1

Col1

234.34

C56.67

2345.67

C67.56

This is throwing an error, can somebody please guide me:

%macro generate ;


    %local i ;

    data table1;

    length Col1 $100.;

    %do i=1 %to %sysfunc(countw(&abc.));

          Col1 = %scan(&abc., &i.,,"sq");
          output;

     %end;
    run;

%mend;

%generate;

Upvotes: 0

Views: 682

Answers (3)

Tom
Tom

Reputation: 51566

You can do this with a DO loop, but you will need to remove the () from the value of the macro variable. 

%let abc = ("234.34", "C56.67", "2345.67", "C67.56") ;
data table1;
  length Col1 $100.;
  do Col1 = %substr(&abc,2,%length(&abc)-2);
    output;
  end;
run;

If the values do not contain () then you could also just use %scan(&abc,1,()), which also has the advantage of working whether or not the original value has the enclosing ().

Or just remove the () from the value and add them back when you use the macro variable in place where they are needed.

%let abc = "234.34", "C56.67", "2345.67", "C67.56" ;
...
  do Col1 = &abc ;
...
  where dxcode in (&abc) 
....

Upvotes: 0

samkart
samkart

Reputation: 6644

A proc sql way

%let abc = ("234.34", "C56.67", "2345.67", "C67.56") ;

proc sql;
create table tab1 (col1 varchar(100));
quit;

options macrogen mlogic;

%macro gen;
%let i = 1;
%do %while (%scan(%superq(abc),&i,%str(,)) ne %str( ));

proc sql;
insert into tab1
values(%scan(%superq(abc),&i,%str(,)));
quit;

%let i = %sysevalf(&i + 1);
%end;

%mend gen; %gen;

It would scan through your macro that is delimited by ",", and progress by 1 with each loop.

Upvotes: 0

Kiran
Kiran

Reputation: 3315

you do not need a macro and one way to do this.

%let abc = ("234.34", "C56.67", "2345.67", "C67.56" );
%let abc = %qsysfunc(compress(&abc,%str(%"%)%()));
data table1;
length Col1 $100.;
do i = 1 to countw("&abc", ","); 
Col1 = scan("&abc", i,','); 
output; 
end;
run;

Upvotes: 2

Related Questions