BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Neeta
Fluorite | Level 6

Hi all,

I'm working on a data management query report. I've a dataset which stores information about variables where query was placed. Based on that information, I've to find current value of the quaried variable.

 

data query;
input ptid visit source_data $ var $;
cards;
1 10 PE HEIGHT
1 10 AE AEPT
1 999 DM RACE
2 20 PE WEIGHT
;
run;

 

data PE;
input ptid visit HEIGHT WEIGHT;
cards;
1 10 5 60
1 20 6 80
1 30 4 40
2 10 5 60
2 20 6 80
2 30 4 40
;

data AE;
input ptid visit AEPT $ AESEV;
cards;
1 10 COUGH 1
1 20 DIZZY 2
1 30 FATIGUE 3
2 10 FEVER 4
2 20 NAUSEA 3
2 30 VOMIT 1
;

data DM;
input ptid visit RACE $;
cards;
1 999 ASIAN
2 999 AMERICAN
;

 

Here is desired output data:

 

data desired;
input ptid visit source_data $ var $ current $;
cards;
1 10 PE HEIGHT 5
1 10 AE AEPT COUGH
1 999 DM RACE ASIAN
2 20 PE WEIGHT 80
;
;
run;

 

In reality, I've hundreads of records and ~ 50 variables in each dataset. I would like to know efficient way of solving this problem!

Thanks in advance!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

So let's see how we could find the records you want from the PE dataset.  Something like this might do the trick.

data next ;
  length ptid visit 8 source_data var $32 current $32 ;
  keep ptid -- current ;
  set pe ;
  retain source_data 'PE';
  if ptid=1 and visit=10 then do;
    var = 'HEIGHT';
    current=vvalue(HEIGHT);
    output;
  end;
  if ptid=2 and visit=20 then do;
    var = 'WEIGHT';
    current=vvalue(WEIGHT);
    output;
  end;
run;

So then it should be easy to generate code like that for each of the source datasets.

So then just generate one of those for each value of SOURCE_DATA in the metadata file. Along with some PROC APPEND code to aggregate them.

proc sort data=query ;
  by source_data ptid visit var ;
run;

filename code temp;
data _null_;
  set query;
  by source_data ;
  file code ;
  if _n_=1 then put
 'proc delete data=want;'
/'run;'
  ;
  if first.source_data then put 
 'data next ;'
/'  length ptid visit 8 source_data var $32 current $32 ;'
/'  keep ptid -- current ;'
/'  set ' source_data ';'
/'  retain source_data ' source_data :$quote. ';'
  ;
  put
 '  if ' ptid= 'and  ' visit= 'then do;'
/'    var = ' var :$quote. ';'
/'    current=vvalue(' var ');'
/'    output;'
/'  end;'
  ;
  if last.source_data then put
 'run;'
/'proc append base=want data=next force;'
/'run;'
  ;
run;

%include code / source2 ;

proc print; run;
                        source_
Obs    ptid    visit     data       var        current

 1       1       10       AE       AEPT      COUGH
 2       1      999       DM       RACE      ASIAN
 3       1       10       PE       HEIGHT               5
 4       2       20       PE       WEIGHT              80

 

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

Not sure what you mean by CURRENT value, but it looks like you have simple code generation problem.

Figure out what code you would need to get your desired result and then figure out how to generate that code from your input metadata table.  Since your queries will need to analyze many input tables you might want to do it either one record of your metadata table at a time.  But you could also possible group the metadata by the source data table and try to generate the results for all queries against that table at the same time.

Neeta
Fluorite | Level 6

Thanks for reply. CURRENT value is nothing but value of that variable for given ptid at that visit. 

Tom
Super User Tom
Super User

So let's see how we could find the records you want from the PE dataset.  Something like this might do the trick.

data next ;
  length ptid visit 8 source_data var $32 current $32 ;
  keep ptid -- current ;
  set pe ;
  retain source_data 'PE';
  if ptid=1 and visit=10 then do;
    var = 'HEIGHT';
    current=vvalue(HEIGHT);
    output;
  end;
  if ptid=2 and visit=20 then do;
    var = 'WEIGHT';
    current=vvalue(WEIGHT);
    output;
  end;
run;

So then it should be easy to generate code like that for each of the source datasets.

So then just generate one of those for each value of SOURCE_DATA in the metadata file. Along with some PROC APPEND code to aggregate them.

proc sort data=query ;
  by source_data ptid visit var ;
run;

filename code temp;
data _null_;
  set query;
  by source_data ;
  file code ;
  if _n_=1 then put
 'proc delete data=want;'
/'run;'
  ;
  if first.source_data then put 
 'data next ;'
/'  length ptid visit 8 source_data var $32 current $32 ;'
/'  keep ptid -- current ;'
/'  set ' source_data ';'
/'  retain source_data ' source_data :$quote. ';'
  ;
  put
 '  if ' ptid= 'and  ' visit= 'then do;'
/'    var = ' var :$quote. ';'
/'    current=vvalue(' var ');'
/'    output;'
/'  end;'
  ;
  if last.source_data then put
 'run;'
/'proc append base=want data=next force;'
/'run;'
  ;
run;

%include code / source2 ;

proc print; run;
                        source_
Obs    ptid    visit     data       var        current

 1       1       10       AE       AEPT      COUGH
 2       1      999       DM       RACE      ASIAN
 3       1       10       PE       HEIGHT               5
 4       2       20       PE       WEIGHT              80

 

Neeta
Fluorite | Level 6
Thanks a lot Tom! Your solution worked perfectly!!!
Reeza
Super User

I don't see anything here besides the need for a join/merge.

 

Do you need help with understanding how to do that? 

https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-in-sas/

 

Though to be honest, usually most people who can't join can't prep sample data so is this homework or work?

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
  • 5 replies
  • 827 views
  • 2 likes
  • 3 in conversation