topic Re: Macrowise variables in datastep in SAS Programming

Macrowise variables in datastep

piddy — Mon, 02 May 2016 18:19:23 GMT

Hi everybody

The code below is running as it should, it just fills up a lot of space 🙂

I have 4 variables in which I use the function prxchange to make certain expression similar on all observations and for the 4 variables

and then in the end use the CATX function to rearrange the final variables as I want them to be.

I would like to just write the code once, and not writing it 4 times because of the 4 variables. I hope someone can help me with that.

Thanks in advance.

%let kø =NEW_QUEUE; %put &kø;

%let koe =QUEUE; %put &koe;

%let trin =SAGSTRIN; %put &trin;

%let strin =SAGSTRINNAVN; %put &strin;

data &navn

(drop= &kø &kø._2 &kø._3 &kø._4 XREF_clean1

&koe &koe._2 &koe._3 &koe._4 XREF_clean2

&trin &trin._2 &trin._3 &trin._4 XREF_clean3

&strin &strin._2 &strin._3 XREF_clean4

rename =(&kø._5=NEW_QUEUE &koe._5=QUEUE &trin._5=SAGSTRIN &strin._4=SAGSTRINNAVN Ansvarlig=Ini);

set wfstat.sagstider (keep=&varliste);

length &kø &kø._2 &kø._3 &kø._4 &kø._5

&koe &koe._2 &koe._3 &koe._4 &koe._5

&trin &trin._2 &trin._3 &trin._4 &trin._5

&strin &strin._2 &strin._3 &strin._4 $ 60;

start_date =datepart(trin_start);

slut_date =datepart(wf_slut);

Md_afslut =month(slut_date);

Year =year(slut_date);

*Changing XREF step (NEW_QUEUE), so it's the same for all oberservations;

&kø._3 = left(prxchange('s/[(]|[)]/ /i',-1,trim(upcase(&kø._2))));

if prxmatch("m/XREF/oi",&kø._3) then XREF_clean1="XREF";

&kø._4 = left(prxchange('s/XREF//i',-1,trim(upcase(&kø._3))));

&kø._5 = CATX(" - ",XREF_clean1,propcase(&kø._4));

*Changing XREF step (QUEUE), so it's the same for all oberservations;

&koe._3 = left(prxchange('s/[(]|[)]/ /i',-1,trim(upcase(&koe._2))));

if prxmatch("m/XREF/oi",&koe._3) then XREF_clean2="XREF";

&koe._4 = left(prxchange('s/XREF//i',-1,trim(upcase(&koe._3))));

&koe._5 = CATX(" - ",XREF_clean2,propcase(&koe._4));

*Changing XREF step (SAGSTRIN), so it's the same for all oberservationst;

&trin._3 = left(prxchange('s/[(]|[)]/ /i',-1,trim(upcase(&trin._2))));

if prxmatch("m/XREF/oi",&trin._3) then XREF_clean3="XREF";

&trin._4 = left(prxchange('s/XREF//i',-1,trim(upcase(&trin._3))));

&trin._5 = CATX(" - ",XREF_clean3,propcase(&trin._4));

*Changing XREF step (SAGSTRINNAVN), so it's the same for all oberservations;

if prxmatch("m/XREF/oi",&strin._2) then XREF_clean4="XREF";

&strin._3 = left(prxchange('s/XREF//i',-1,trim(upcase(&strin._2))));

&strin._4 = CATX(" - ",XREF_clean4,propcase(&strin._3));

format start_date slut_date date9.;

run;

Re: Macrowise variables in datastep

Astounding — Mon, 02 May 2016 19:37:22 GMT

This isn't a solution by any means, but a suggestion. You don't need so many variables. Your program would be easier to work with now, and easier to turn into a macro, if you reused variable names. For example, use _2 instead of &prefix._2. Use _3 instead of &prefix._3. You're only going to drop these variables anyway, so don't make up separate names for each prefix.

Re: Macrowise variables in datastep

FreelanceReinh — Mon, 02 May 2016 21:03:12 GMT

Hi again, @piddy,

To me, this looks like a good application for a user-defined CALL routine. With that you would no longer need

macro variables kø, koe etc.
the long DROP= dataset option
most parts of the RENAME= dataset option
temporary variables in the data step (see LENGTH statement)
long repetitive code

So, your code would be much shorter, easier to understand and easier to maintain. Moreover, you could separate the code for the definition of the CALL routine from the code where the routine is applied and you could reuse the CALL routine in other programs (if this makes sense).

Here is a simple example: a CALL routine that removes digits and, optionally, parentheses from the contents of a character variable:

/* Define CALL routine CLEANSTR */

proc fcmp outlib=work.funcs.test;
subroutine cleanstr(c $, m $);  /* c: character variable to be "cleaned"     */
  outargs c;                    /* m: modifier to trigger optional features, */
                                /*    here: removal of parentheses           */
  c2=prxchange('s/\d//o',-1,c);
  if upcase(m)='P' then c2=prxchange('s/\(|\)//o',-1,c2);
  c=c2;
endsub;
quit;

options cmplib=work.funcs; /* make it available */

/* Create test data */

data have;
var1='1ABC(29)';
var2='x(1)-y(2)';
run;

/* Apply the new CALL routine */

data want;
set have;
call cleanstr(var1,'');
call cleanstr(var2,'p');
proc print;
run;

I think, with the documentation you should be able to adapt this example to your needs. Please feel free to get back to us if you encounter any problems.

Re: Macrowise variables in datastep

piddy — Mon, 02 May 2016 21:35:51 GMT

That's quite clever. 😀 Will use that. Have a deadline soon but will incoporate that later. Thank you a lot.

As a side kick as a new beginner to SQL programming are there any books / websites you would recommend?

Re: Macrowise variables in datastep

FreelanceReinh — Mon, 02 May 2016 22:23:22 GMT

@piddy wrote:

As a side kick as a new beginner to SQL programming are there any books / websites you would recommend?

If you mean PROC SQL (that's the only flavor of SQL I use):

I started with (a similar version of) the article PROC SQL for DATA Step Die-hards by Christianna S. Williams and continued with the book PROC SQL by Example by Howard Schreier and some other articles I came across on the web over the years. Of course, this forum has also a lot of interesting posts about PROC SQL, but a systematic approach like in the resources I mentioned is more useful at the beginning.