BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser123123
Quartz | Level 8
Hello!

So I've data which contains of.

Data new;
Input subj test $ result Date
Data lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
so I need the New variable "NEW" that contains only latest date of 'SYS' test only. Could you please tell me how to get that variable.


Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

Do you mean this?

 

Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, max((test='SYS')*date) as max format=yymmdd10.
from new
group by subj;
quit;

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

Do you mean this?

 

Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, max((test='SYS')*date) as max format=yymmdd10.
from new
group by subj;
quit;

Amir
PROC Star

As @novinosrin asked not sure if you want the whole column set to the max date for "SYS", or if you just want a macro variable with the value.

 

The following creates a new column using a data step alternative:

 

data have;
   input subj
         test   $
         result
         date   yymmdd10.
   ;

   format date date9.;

   datalines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;

/* use double DOW loop */
data want;
   do until (last_check);
      set have end = last_check;
      if test eq 'SYS' then
         new = max(new,date);
   end;

   format new date9.;

   do until (last_obs);
      set have end = last_obs;
      output;
   end;
run;

 

Amir.

sasuser123123
Quartz | Level 8
Hello @novinosrin !
I run the program which you gave. So the new column max contains the maximum date for all tests but I need only for test 'SYS' .
How do that one.
Amir
PROC Star

What value should be in the new column for the rows that do not have a test value of 'SYS'?

 

Should they be blank or have the same value as the date column or something else?

 

Amir.

novinosrin
Tourmaline | Level 20

Good morning @sasuser123123  A simple tweak 

 

Data new;
Input subj test $ result Date :yymmdd10.;
format date yymmdd10.;
lines;
101 DIA 56 2012-03-25
101 DIA 54 2012-03-28
101 SYS 105 2013-11-23
101 SYS 108 2013-10-10
101 SYS 110 2013-11-29
;
proc sql;
create table want as
select *, ifn(test='SYS',max((test='SYS')*date),.) as max format=yymmdd10.
from new
group by subj;
quit;
sasuser123123
Quartz | Level 8
Thank you for your quick response!!

Yeah, they should be blank (The rows do not have test 'SYS')
Amir
PROC Star

I should have said missing, rather than blank.

 

The following code sets the value to missing, which for numeric values is '.' by default, but if you really want a blank then you can comment out the options statement at the top to change the default from '.' to '', but the value will still be considered as missing.

 

/* change default numeric missing from '.' to '' */
*options missing = '';

/* use double DOW loop */
data want(drop = saved_new);
   do until (last_check);
      set have end = last_check;
      if test eq 'SYS' then
         new = max(new,date);
   end;

   format new date9.;
   saved_new = new;

   do until (last_obs);
      set have end = last_obs;

      if test ne 'SYS' then
         call missing(new);
      else
         new = saved_new;

      output;
   end;
run;

 

Amir.

sasuser123123
Quartz | Level 8
Thank you so much for your assistance

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 10867 views
  • 5 likes
  • 3 in conversation