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
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
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
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.
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
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.
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;
%end;
%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
"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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.