DATA Step, Macro, Functions and more

proc sql - query

Reply
Frequent Contributor
Posts: 76

proc sql - query

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.

SrNoINACTIVE_STATUSBECO_GRADEGECO_GRADEAPPLICATION_CHANNEL_NMWORDWORD_VALUE
SrNoINACTIVE_STATUSBECO
1AINACTIVE_STATUSA
2CINACTIVE_STATUSC
3B1BECO_GRADEB1
4A1GECO_GRADEA1
5CHANNEL_7APPLICATION_CHANNEL_NMCHANNEL_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);

Super User
Posts: 17,912

Re: proc sql - query

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

Super User
Posts: 6,966

Re: proc sql - query

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 172 views
  • 0 likes
  • 3 in conversation