BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Dear

I am trying Proc sql step to reduce my the code in my program.

I need to calculate baseline values for weight and height by comparing dates in ex with  vs.

 

output needed;

id   weightbl  heightbl

1      70                170

 

The vdate should less than edate and i need to select the last obs close to on or before edate. Please help in my code. Thank you.

data vsone;
input id tcd $ vdate $10. value;
datalines;
1 weight 2016-04-06 69
1 weight 2016-04-18 70
1 weight 2016-04-19 .
1 weight 2016-05-18 80
1 height 2016-04-16 169
1 height 2016-04-18 170
;
data exone;
input id edate $19.;
datalines;
1 2016-04-26T13:10
;
proc sql;
create table want as
select *
from vone as a left join eone as b
on a.id=b.id and input(a.vdate, is8601da.)- datepart(input(b.edate, is8601dt.) =< 1
where value ne .
group by id 
having max(input(a.vdate,is8601da.)- datepart(input(b.edate,is8601dt.)));
quit;

  

3 REPLIES 3
ChrisNZ
Tourmaline | Level 20

Your SQL is certainly not much shorter, and absolutely much more difficult to follow than a data step.

 

> the last obs close to on or before edate

processing records in order screams data step.

 

data VSONE;
input ID TCD $ VDATE yymmdd10. VALUE;
datalines;
1 weight 2016-04-06 69
1 weight 2016-04-18 70
1 weight 2016-04-19 .
1 weight 2016-05-18 80
1 height 2016-04-16 169
1 height 2016-04-18 170
run;
data EXONE;
input ID EDATE yymmdd10.;
datalines;
1 2016-04-26T13:10
run;
data WANT;
  retain WEIGHTBL HEIGHTBL; 
  keep ID WEIGHTBL HEIGHTBL; 
  merge VSONE (rename=(VALUE=VALW VDATE=DATEW) where=(TCD='weight' & VALW))
        VSONE (rename=(VALUE=VALH VDATE=DATEH) where=(TCD='height' & VALH))
        EXONE ;
  by ID;  
  if first.ID then call missing( WEIGHTBL, HEIGHTBL);
  if DATEW<=EDATE then WEIGHTBL=VALW;
  if DATEH<=EDATE then HEIGHTBL=VALH;
  if last.ID then output;
run;

WEIGHTBL HEIGHTBL ID
      70      170  1

PGStats
Opal | Level 21

If you insist on SQL then:

 

data VSONE;
input ID TCD $ VDATE yymmdd10. VALUE;
datalines;
1 weight 2016-04-06 69
1 weight 2016-04-18 70
1 weight 2016-04-19 .
1 weight 2016-05-18 80
1 height 2016-04-16 169
1 height 2016-04-18 170
;

data EXONE;
input ID EDATE yymmdd10.;
datalines;
1 2016-04-26T13:10
;

proc sql;
select 
    a.id, b.value as weight, c.value as height 
from 
    exone as a left join
    (select * from vsone where tcd="weight" and value is not missing) as b 
        on a.id=b.id and a.edate>= b.vdate left join
    (select * from vsone where tcd="height" and value is not missing) as c 
        on a.id=c.id and a.edate>= c.vdate
group by a.id
having b.vdate=max(b.vdate) and c.vdate=max(c.vdate);
quit;
PG
s_lassen
Meteorite | Level 14

Rather than hardcoding "height" and weight" I would use transpose to get the final table:

proc sql;

  create table last as

  select vsone.*

  from vsone join exone

    on vsone.id=exone.id and vsone.vdate <= exone.edate

  where value ne .

  group by vsone.id,tcd

  having vsone.vdate=max(vsone.vdate);

quit;

 

proc transpose out=want(drop=_name_) suffix=tbl;

  by id;

  var value;

  id tcd;

run;

I would have used the code window to post code, but for some reason "paste" does not work right now (just got a new Windows version, maybe therefore). 

Rather than convering all the date texts to SAS dates, you can use the fact that they are ordered (both formats start with yyyy-mm-dd). I dropped the aliases, they do save a few keystrokes, but otherwise they just make the program harder to read.

I also dropped the left join. But maybe you want the highest VDATE when there is no EDATE? Then use the left join again, and add "or exone.edate is null" to the where clause.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 981 views
  • 7 likes
  • 4 in conversation