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

Following up the question in my previous post, I would like to ask if any sas expert can help me on this simple question.

I’d like to know how many total excise times for each person six months before the 1st test_date when 2<=score<=4.

BTW, thanks again Haikuo and Arthur for your help on my previous question.  Much appreciate.

Regards.

data test;

input @1 id @4 test_date yymmdd10. @13 score;

format test_date yymmdd10.;

cards;

004 20050615 9

001 20021120 2

002 20030128 3

005 20040917 9

003 20040314 6

001 20030523 7

004 20001225 5

005 20061225 4

002 20010807 7

001 20031010 4

run;

data exercise;

input @1 id @5 exercise_date yymmdd10.;

format exercise_date yymmdd10.;

cards;

001 20011206

001 20030806

010 20030526

001 20020728

008 20051223

002 20021127

003 20040527

002 20020901

005 20050728

005 20050925

002 20041002

006 20061112

001 20020928

001 20030905

run;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

If you only want to narrow the results of your previous run to only the earliest test_date, and you've already run the code, couldn't you just run something like the following on the file you originally produced:

proc sql;

  create table really_want as

    select * from want

      group by id

        having test_date eq min(test_date)

;

quit;

View solution in original post

4 REPLIES 4
art297
Opal | Level 21

If you only want to narrow the results of your previous run to only the earliest test_date, and you've already run the code, couldn't you just run something like the following on the file you originally produced:

proc sql;

  create table really_want as

    select * from want

      group by id

        having test_date eq min(test_date)

;

quit;

Patrick
Opal | Level 21

What about:

proc sql;

  select e.id, count(*) as exercises_6monthPrev_cnt

  from

    (select * from test where 2<=score<=4 group by id having min(test_date)=test_date) t

    , exercise e

  where t.id=e.id and t.test_date >= intnx('month',e.exercise_date,6,'s')

  group by e.id

  ;

quit;

mkeintz
PROC Star

You can use PROC MEANS with the min stat to get the earliest date with 2<=socre<=4 for each id.  Not presorting required:


proc means data=test (where=(2<=score<=4)) min noprint nway;
   class id;
   var_test_date;
   output out=tdates (keep=id test_date)  min=;
run;

** But for most non-hash solutions, the exercise data should be sorted to interleave with the TDATES dataset above: **;

proc sort data=exercise  out=exer_srt;

  by id exercise_date;

run;

** Note: the "set   xxx yyy" with a BY statement always has the XXX data precede the YYY data for a given BY value.**:

data want (keep=id test_date cut_date n_dates);
  do until (last.id);

    set tdates  (in=int)  exer_srt;

    by id;

    if first.id then n_dates=0;

    if int then do;

      cut_date=intnx('month',test_date,-6,'end');

      tdate=test_date;

    end;

    else if exercise_date <= cut_date then n_dates=n_dates+1;

  end;

  if cut_date^=. then do;

    test_date=tdate;

    output;

  end;

  format cut_date date9.;

run;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
user1
Calcite | Level 5

Hi, thanks very much, Arthur, Patrick and Mkeintz, for your prompt response and help!

Regards.

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!

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.

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
  • 4 replies
  • 712 views
  • 6 likes
  • 4 in conversation