Astounding Tracker

Re: When is your SAS program good enough???

Astounding — Sun, 20 Jul 2025 16:45:50 GMT

Agreed, but I like to add one further condition.

When you have simplified it enough so that the people who need to use it can do so easily, without running to you with questions every time.

Re: merging data sets using set statement

Astounding — Tue, 20 May 2025 19:43:57 GMT

In the example you have provided, you don't need the data set A at all. You coudl simply forget about merging and just use:

data C;
set B;
run;

Please include an example where the data set A is needed.

Re: sort by multiple numeric var or one char that is concatenation

Astounding — Tue, 20 May 2025 14:25:59 GMT

So you posted the original question 4 hours ago. Are you really saying that it would have taken longer than 4 hours to test it yourself?

Re: converting numeric zip codes of varying lengths

Astounding — Fri, 16 May 2025 21:59:27 GMT

First question is what do you want as the result? Should the longer values be rounded:

77778

10000

Or should they be truncated:

77777

99999

Assuming they should be truncated, you would need to change the values before applying a format (such as z5.):

data want;
set sample;
if num_zip > 99999 then do until (num_zip <= 99999);
   *Yes, there are more compact ways to do this;
   num_zip = int(num_zip / 10);
end;
zipcode = put(num_zip, z5.);
run;

Re: Thoughts and questions: why this kind of macro is so difficult to debug.

Astounding — Sun, 04 May 2025 16:48:49 GMT

Does the original question insist on using the IN operator? It would be mildly simpler to use %INDEX:

%macro ctrylist(ctry1);
%let list1=AT AU CA CH;
%put &list1;
%if %index(&list1, &ctry1) %then %do;
   %put &ctry1;
%end;
%else %do;
   %put ERROR: &ctry1 is an invalid country code.;
   %put ERROR: Valid country codes include &list1..;
%end;
%mend ctrylist;
%ctrylist(AU)
%ctrylist(zz)

In general, if you start with simpler tools, adding the complexity of macro language can produce a simpler result.

Re: Comparing date of death to date generated by macro

Astounding — Mon, 07 Apr 2025 16:49:32 GMT

Just addressing the question you asked (no comment on the approach or the programming tools used), this would need to change:

IF date <= put(31DEC20&yy.) THEN delete;

Here's the correct way to refer to a particular date in SAS:

IF date <= put("31DEC20&yy."d) THEN delete;

But really you don't even need to go that far. Refer to the date in your programming statements:

if date <= "31DEC20&yy."d then delete;

No functions needed.

Re: macro quoting issue for list of values

Astounding — Thu, 03 Apr 2025 13:15:02 GMT

@PaigeMiller , thanks for the mention. Unfortunately the book "Macro Language Magic" is now out of print, and I have been retired and without SAS for more than 5 years now.

Off the top of my head, the most common "mysterious" need for %unquote is when macro language creates a list of quoted items for use in a WHERE clause within SQL code. Many times the software figures out to unquote a list, but not when SQL is involved.

Re: Why I always get 3:4:5

Astounding — Thu, 03 Apr 2025 13:08:15 GMT

Not the same thing but, it's my favorite "audience bet".

Playig backgammon, and rolling two dice, rolling "doubles" is advantageious (1:1, 2:2, ... 6:6).

Chances of doubles on a single roll is 1 out of 6. I claim that my opponent is exceedingly lucky and if he rolls the dice only 5 times, he will roll doubles at some point.

The math is much easier than the question with three colors of balls.

Re: Please help find why I cannot call this Macro m105p02.sas !

Astounding — Fri, 14 Mar 2025 19:13:35 GMT

A couple of good starting points ...

As others have pointed out, we don't know what the log says, so we are only guessing ...

This into: phrase looks incorrect:

into :Basin1-, :BasinName-

It should likely be:

into :Basin1-, :BasinName1-

And later when using the macro variables, are the basins actually numbers, or are they character strings? For numbers, your code is correct:

where Basin=&&Basin&i;

But for character strings you would need to add doublequotes:

where Basin="&&Basin&i";

Re: keep two separate values in one row

Astounding — Fri, 28 Feb 2025 08:56:22 GMT

Here's a variation that gives you all the data you might ever want ... but lets you postpone the typing of all the variable names until you know the numbers you want. There is no way to avoid naming the variables you want however. Apologies for stealing bits of code here and there from earlier posts.

proc summary data=have nway;
   class id;
   var _numeric_;
   output out=minimums min=;
   output out=maxiimums max=;
run;

This gives you two new data sets (I'll describe just one). MINIMUMS contains one observation per ID, with the smallest value of each numeric variable. You don't have to name any of them, because this program re-uses the same variable name. In MINIMUMS, there is one observation per ID, and EVENT_DT holds the minimum value of EVENT_DT from the original data. So the same variable name gets re-used without any typing or inventing names on your part.

You can always decide later which of the output pieces you want to use in your report. The price you pay for this simplicity is on the back end when it is more complex to select the pieces that you want. Here are the hoops you might have to jump through when using the data:

data want;
    merge have 
          minimums (keep=id event_dt rename=(event_dt=firstdt))
          maximums (keep=id event_dt rename=(event_dt=lastdt));
   by id;
run;

You start with simplicity, as well as great flexibility. But you can't totally get around your responsibility to name the variables you want to use.

Re: Creating new variables based on most recent date

Astounding — Thu, 20 Feb 2025 04:44:13 GMT

Whichever function you choose (dif or lag), make sure it executes on every observation. If you skip observations where first.med is 0, the next observation will have an incorrect calculation.

Re: clarification in practice exam questions

Astounding — Tue, 04 Feb 2025 03:50:12 GMT

Can't be sure what your question is. But one fault with your program is that you sort the data set output27a. But your program doesn't create that data set, it only creates results.output27a. Just a typo? Or truly a different data set?

Re: %If %then do %end %else do

Astounding — Mon, 03 Feb 2025 16:12:02 GMT

Definitely a problem. While unbalanced single quotes can create problems, this is a call to the macro DON

%don

The simplest version of this macro might be:

%if &A=0 or &B=0 %then %createtable;

Re: How to display name of the programme (for example l_ae.sas) inside the output which it is creati

Astounding — Mon, 03 Feb 2025 09:51:44 GMT

One approach:

title "Currently the program is %sysfunc(getoption(sysin))";

Also see:

https://communities.sas.com/t5/SAS-Programming/Get-program-name-and-path-when-using-INCLUDE/td-p/529145

Re: Trap when merging, then redefining variable

Astounding — Wed, 29 Jan 2025 14:33:05 GMT

Here are some ancient papers that discuss this and similar issues.

Here's an ancient paper that discusses your original post plus more:

How MERGE Really Works

https://www.lexjansen.com/nesug/nesug99/ad/ad155.pdf

And here's a slightly less ancient paper that discusses other bizarre results with MERGE:

Danger: MERGE Ahead! Warning: BY Variable with Multiple Lengths

https://support.sas.com/resources/papers/proceedings/proceedings/sugi28/098-28.pdf

Re: calculate all possible combinations to calculate PD

Astounding — Wed, 22 Jan 2025 04:47:22 GMT

While I have solved similar problems, I have never seen a variable called VALUE. What does it mean?

Re: IF THEN DO

Astounding — Mon, 20 Jan 2025 20:45:48 GMT

Not sure if this is a complete fix, but it's an issue that stands out.

If your first IF THEN finds a match:

if year = "2024" and wk1 = "wk53" then do; 
                                        wk1 = "wk01";  
                                        wk2 = "25_wk01"; 
 									   year = "2025"; 
 									   mth2 = "01"; 
 									   end;

the program doesn't stop. It keeps going through the remaining logic. So the observation that meets the first set of conditions will automatically meet the third set of conditions:

if year = "2025" and wk1 = "wk01" then do; 
                                        wk1 = "wk02";  
                                        wk2 = "25_wk02"; 
 									   year = "2025"; 
 									   mth2 = "01"; 
 									   end;

If that's the issue, you will need to familiarize yourself with ELSE before an IF THEN.

Re: Macro within Macro

Astounding — Sat, 18 Jan 2025 10:01:54 GMT

A general principle: get your code working without macro language. Once that is complete, convert your code to a macro. Looking at your program, a couple of things would stand out.

The letter "n" is missing from the word "then": "the do"

The IN operator is being used incorrectly. A single value can precede the word IN, with a list of values following the word IN.

It is unlikely you want to include parentheses within your list of values, as would happen in your program.

Also note (this could change the way you structure your program), following the IN operator you are allowed to use an array reference

Good luck.

Re: Need help to create a duplicate record report

Astounding — Tue, 31 Dec 2024 18:42:54 GMT

How should this cases be handled?

Some missing values, but all non-missing values are identical

Re: I have multiple dates in one column. How I can add them in separate columns.

Astounding — Tue, 31 Dec 2024 18:36:20 GMT

Agree with the advice. A small tweak to the solution:

 do i=1 to 1 + countw(dates,',');