need help on sas code

Reply
Occasional Contributor
Posts: 16

need help on sas code

Hi, can anyone help me on this? I have two datasets below.  I want to get a final table which will include a variable about the total times of exercise six months before the test_date when score 2<=score<=4.

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;

Thanks for your help and have a nice weekend, everyone!

Respected Advisor
Posts: 3,156

Re: need help on sas code

Question: 'six months before the test_date' meaning?

1. if test_date is Dec, then Jul <=target<=Dec?

2. If test_date is Dec, then target<=June?

Anyway, the following code will based on assumption 1. If either, do please provide sample outcome table.

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

;

proc sql;

create table want as

select id, test_date,count(*) as ct from

(select  a.id,test_date,exercise_date from test a, exercise b where a.id=b.id and 2<=score<=4 and  intnx('day', test_date,-180)<=exercise_date <= test_date)

group by id, test_date;

quit;

proc print;run;

Haikuo

PROC Star
Posts: 7,467

Re: need help on sas code

I think this produces the same result as Haikuo's code, buy may be easier to read and run slightly faster:

proc sql;

  create table want as

    select distinct a.*,count(*) as exercise_days

      from test as a, exercise as b

        where a.id=b.id and

          exercise_date between

            intnx('month',test_date,-6,'s') and test_date

          and score between 2 and 4

          group by a.id,a.test_date

  ;

quit;

Occasional Contributor
Posts: 16

Re: need help on sas code

Thanks Hai.Kuo and Arthur for your help!

BTW, the assumption is if test_date is Dec 1st, then count exercise_times between test_date (Dec 1st) - 180 days.

Regards.

PROC Star
Posts: 7,467

Re: need help on sas code

BTW: I stand corrected.  Haikuo's code runs faster than what I had proposed.

Occasional Contributor
Posts: 5

Re: need help on sas code

Arthur,

As per several requests, please stop including me in your replies to other peoples' questions. I do not how else I can ask you for this. Or may be there is a way that I can do it in SAS web site and I am not aware of it. If it is  the case, anyone, please reply to this email. It has becoming so annoying. As a community, we need to support each other, not harassing.

Thank you all including Arthur if you would read this.

PROC Star
Posts: 7,467

Re: need help on sas code

Posted in reply to eotarod03_nni_com

Ellaheh,

I have no idea what you are talking about, don't recall including you in any replies, and have never intentionally harassed anyone.  Possibly you have your personal settings aligned to get notifications from posts made by certain and/or all respondents to the forum.  I, personally, turned off all such notifications as I was getting notifications every time anyone posted anything to the forum.

Art

Respected Advisor
Posts: 3,156

Re: need help on sas code

Hi,

My code has just been updated from 6month to 180 days.

Haikuo

Occasional Contributor
Posts: 16

Re: need help on sas code

Thanks again for your great help!

Respected Advisor
Posts: 3,156

Re: need help on sas code

Just like Art hinted in his post, although SQL is the intuitive reaction for this kind of problem, it does sometimes impose a performance issue. Here is a Hash() approach, which is much faster than SQL (0.01 sec vs 0.55 sec on my machine):

data want;

  if _n_=1 then do;

     if 0 then set exercise;

     dcl hash h(dataset:'exercise', multidata:'y');

     h.definekey('id');

     h.definedata(all:'y');

     h.definedone();

    end;

    set test (where=(2<=score<=4));

    ct=0;

    do rc=h.find() by 0 while (rc=0);

    if intnx('day', test_date,-180)<=exercise_date <= test_date then  ct+1;

       rc=h.find_next();

    end;

    if ct>0 then output;

    keep id test_date ct;

    run;

Haikuo

Occasional Contributor
Posts: 16

Re: need help on sas code

mine was 0.21 sec. Thanks again Hai.Kuo.

Occasional Contributor
Posts: 16

Re: need help on sas code

I'd like to ask how to adjust the code to count the total excise_times if only the last test_date when 2<=score<=4 is considered. Thanks very much again!

Regards.

Ask a Question
Discussion stats
  • 11 replies
  • 507 views
  • 6 likes
  • 4 in conversation