proc sql - query

[aj34321]

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

[Reeza]

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

[Kurt_Bremser]

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.