BookmarkSubscribeRSS Feed
aj34321
Quartz | Level 8

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

2 REPLIES 2
Reeza
Super User

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

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 670 views
  • 0 likes
  • 3 in conversation