proc sql - query

aj34321 — Wed, 04 Mar 2015 00:43:08 GMT

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

Re: proc sql - query

Reeza — Wed, 04 Mar 2015 00:47:46 GMT

Why SQL? I would think a Data step would be more efficient in this situation.

Re: proc sql - query

Kurt_Bremser — Wed, 04 Mar 2015 06:51:44 GMT

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.

topic proc sql - query in SAS Programming

proc sql - query

Re: proc sql - query

Re: proc sql - query