topic Re: Comparing one dataset with another without merging (with the help of macro) in SAS Programming

Comparing one dataset with another without merging (with the help of macro)

JatinBansal — Tue, 24 Dec 2013 09:11:51 GMT

Hi All

I have an interview question to get an answer for. The only condition is we are not to use merging for the following and to do it with the help of macros only.

Question: There are two datasets. One is having Subject and visit, other is having visit only.

data t;

input subject visit @@;

datalines ;

001 1 001 2 001 3 001 8 002 1 002 8

;

data r;

input visit @@;

datalines ;

1 2 3 4 5 6 7 8

;

I want another dataset which will compare the visit from first dataset to the second dataset and will give the missing visits in new variable, one observation for each missing visit for each subject. Following is the example of final output required:

subject visit missingvisit

001 1

001 2

001 3

001 4

001 5

001 6

001 7

001 8

Reminder - not to use the merging

Thanks everyone

Re: Comparing one dataset with another without merging (with the help of macro)

NaveenSrinivasan — Tue, 24 Dec 2013 10:44:16 GMT

Hi,

Since you have mentioned using Macros only, I'd think of using CALL SYMPUT to place your variable values into a macro variable and call the same(key variable) using SYMGET function. I'd suggest do it in two steps for easy understanding. The syntax is pretty simple as I am sure you would have noticed in support.sas.com.

Hope that helps.

Thanks,

Naveen

Re: Comparing one dataset with another without merging (with the help of macro)

data_null__ — Tue, 24 Dec 2013 11:20:27 GMT

I don't know what macro will bring to this party. I like the features of PROC SUMMARY for this. If you have other variables in T as you surely would you can use IDGROUP to pass then through.

data t;
   input subject visit @@;
   y = ranuni(1);
   y2 = sqrt(y);
   datalines; 
001 1 001 2 001 3 001 8 002 1 002 8
; 

data r;
   input visit @@;
   datalines; 
1 2 3 4 5 6 7 8
; 

proc summary data=t classdata=r nway; 
   by subject;
   class visit;
   output out=allvis(drop=_type_) idgroup(out(Y:)=);
   run; 
proc print; 
   run;

Re: Comparing one dataset with another without merging (with the help of macro)

JatinBansal — Tue, 24 Dec 2013 11:56:49 GMT

Hi Naveen

Me and my friend (who was asked this question) also thought that this is very simple 😉

We were on the same track which you suggests i.e. call symput and symget. But can u please give the exact code, how it will take place....

Thanks

Re: Comparing one dataset with another without merging (with the help of macro)

JatinBansal — Tue, 24 Dec 2013 12:02:39 GMT

Hi data_null_

The method you provided, as i understood created a flag and after this it is simple. But the first thing to it is to use the macro. I tried to use call symput to put each value in a macro variable then i got stuck on how to compare it like vlookup.

I appreciate your effort on this. But we need to do this by macro only, this time.

Regards

Re: Comparing one dataset with another without merging (with the help of macro)

data_null__ — Tue, 24 Dec 2013 12:08:39 GMT

The first rule of macro programming in SAS; is to learn when you don't need it or perhaps more importantly when you don't want to use it.

Re: Comparing one dataset with another without merging (with the help of macro)

Tom — Tue, 24 Dec 2013 15:42:07 GMT

"Macro only" is almost never the answer when he problem involves manipulation of data. For that you need to use data steps and procedures.

This is certainly one of those cases and you should have told them such.

Re: Comparing one dataset with another without merging (with the help of macro)

Vince28_Statcan — Tue, 24 Dec 2013 16:14:09 GMT

In a similar trend to DN and Tom,

Had I been asked this question in an interview with my current knowledge of SAS, my answer would be straight forward:

I will assume this is a trick question. Whether this can be achieved or not through macros is irrelevant, there are significantly better and more efficient tools for this task than macros and in particular, any macro strict solution for this task should be avoided.

As for a bad answer to a trick question for the sole sake of showing skills with SAS macros,

%macro really();

proc sql;

select distinct subject

into :subj1-:subj999999

from t

;

quit;

%let nbyvar=&sqlobs;

data _null_;

set t;

call symput(cats('visit', '_', subject, '_', visit), '0'); /* the actual value put in the macro var is irrelevant we only need it to exist. */

run;

proc sql;

select visit

into :v1-:v999999

from r

;

quit;

%let nvisit=&sqlobs;

data want;

%do i=1 %to &nbyvar;

%do j=1 %to &nvisit;

%if %symexist(visit_&i._&j.) %then %do;

subject="&i.";

visit=&j.;

missingvisit=.;

output;

%end; %else %do;

subject="&i.";

visit=.;

missingvisit=&j.;

output;

%end;

run;

%mend;

%really();

Don't get me wrong, it's rarely the best choice to talk against a question but if the rationale as to why macros shouldn't be used to solve this is accompanied by something like "but I assume this is merely for the sake of seeing if I am comfortable with handling macro variable and logic", then here's an example solution that uses right about as much macros as can be. It is probably wiser to demonstrate to the interviewer that not only do you know macro wells but also know when not to macro at the same time~

Vince