I have this macro to which I'm passing few variable names and i simple want to avoid executing proc sql lines explicitly.
Need a script which will make this 4 update sql's in just one.
proc sql;
Update MyTable set word_value = INACTIVE_STATUS where word = 'INACTIVE_STATUS';
Update MyTable set word_value = BECO_GRADE where word = 'BECO_GRADE';
Update MyTable set word_value = GECO_GRADE where word = 'GECO_GRADE';
Update MyTable set word_value = APPLICATION_CHANNEL_NM where word = 'APPLICATION_CHANNEL_NM';
quit;
Request if someone can help on this.
MyTable has below structure: Notice here my word columns tells me from which column the word_value should get the value from.
SrNo | INACTIVE_STATUS | BECO_GRADE | GECO_GRADE | APPLICATION_CHANNEL_NM | WORD | WORD_VALUE |
---|---|---|---|---|---|---|
SrNo | INACTIVE_STATUS | BECO | ||||
1 | A | INACTIVE_STATUS | A | |||
2 | C | INACTIVE_STATUS | C | |||
3 | B1 | BECO_GRADE | B1 | |||
4 | A1 | GECO_GRADE | A1 | |||
5 | CHANNEL_7 | APPLICATION_CHANNEL_NM | CHANNEL_7 |
%macro GenerateSummary(sClass);
data SummaryVars;
length wordstr $50;
drop string;
string = "&sClass.";
do until(wordstr=' ');
count+1;
_type_ = 2 ** (count-1);
wordstr = scan(string, count);
output;
end;
proc sql;
delete from SummaryVars where wordstr = '';
quit;
run;
proc sql;
Update MyTable set word_value = INACTIVE_STATUS where word = 'INACTIVE_STATUS';
Update MyTable set word_value = BECO_GRADE where word = 'BECO_GRADE';
Update MyTable set word_value = GECO_GRADE where word = 'GECO_GRADE';
Update MyTable set word_value = APPLICATION_CHANNEL_NM where word = 'APPLICATION_CHANNEL_NM';
quit;
%mend;
%GenerateSummary(APPLICATION_CHANNEL_NM GECO_GRADE BECO_GRADE INACTIVE_STATUS);
Why SQL? I would think a Data step would be more efficient in this situation.
Instead of the proc sql:
data mytable;
set mytable;
select (word);
when ('INACTIVE_STATUS') word_value = inactive_status;
when ('BECO_GRADE') word_value = beco_grade;
when ('GECO_GRADE') word_value = geco_grade;
when ('APPLICATION_CHANNEL_NM') word_value = application_channel_nm;
otherwise;
end;
run;
The main difference being that the data step needs additional disk space for the temporary copy of my_table, but completes its work in one sweep through the data.
And I completely miss what your macro should achieve. The sql is static code, and summaryvars has no inluence on it.
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.
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.