Desktop productivity for business analysts and programmers

how can we add columns in the existing dataset based upon certain conditions

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

how can we add columns in the existing dataset based upon certain conditions

I have the following data and what I want to do is to add 'SF=0' in all the 5 rows above SF=1 and SF=1 in the below 5 row such that the output looks like something as in the table shown at the last:

 

data havem1(index=(date));
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time : hhmmss5. DIAM	$ PXMC	$ SF;
format date date9. time hhmm5.;
cards;
7101 7101 US000521 1 Works1 08Nov2016 11:58	890,3 s1 1
7102 7102 US000361 1 Works2 02Nov2016 13:01	878,1 s2 1
7102 7102 UC000348 2 Works3 07Nov2016 18:22 877,3 s1 1
7106 7106 UC00424 1 Works1 05Oct2016 9:43 890,4 s1 1
7106 7106 UC00437 3 Works2 07Nov2016 18:23 877,1 s1 1
7106 7106 UC309	4 Works3 07Nov2016 18:26 877,8 s1 1
7107 7107 UC05327 1 Works1 06Oct2016 8:41 837 s1 1
7107 7107 UC200 2 Works2 13Oct2016 12:53 890,55 s1 1
7108 7108 UC000361 3 Works1	02Nov2016 13:01 878,1 s1 1
7108 7108 UC00432 1 Works2 07Nov2016 18:25 877,8 s1 1
7108 7108 UC106 2 Works3 03Oct2016 9:37	890,3 s1 1
;
run;

data wantm1;
 set havem1;
 array x{1} _temporary_;array z{1} _temporary_;
 array y{1} $ 40 _temporary_;
 x{1}=date;z{1}=SF;y{1}=TPMC;
 call missing(of _all_);
 TPMC=y{1};Time=0;SF=z{1};
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 set havem1 point=_n_;output;
 call missing(of _all_);
 TPMC=y{1};Time=0;SF=z{1};
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;

 

Output wanted:

 

TPMCPWCPWSCSiteETDateTimeDIAMPXMCSFBefore_SFAfter_SFBA_SF
7101    03Nov20160:00   0 0
7101    04Nov20160:00   0 0
7101    05Nov20160:00   0 0
7101    06Nov20160:00   0 0
7101    07Nov20160:00   0 0
71017101US0005211Works108Nov201611:58890,3s11  1
7101    09Nov20160:00    11
7101    10Nov20160:00    11
7101    11Nov20160:00    11
7101    12Nov20160:00    11
7101    13Nov20160:00    11
7102    28Oct20160:00   0 0
7102    29Oct20160:00   0 0
7102    30Oct20160:00   0 0
7102    31Oct20160:00   0 0
7102    01Nov20160:00   0 0
71027102US0003611Works202Nov201613:01878,1s21  1
7102    03Nov20160:00    11
7102    04Nov20160:00    11
7102    05Nov20160:00    11
7102    06Nov20160:00    11

Accepted Solutions
Solution
‎11-21-2016 11:18 AM
Grand Advisor
Posts: 9,571

Re: how can we add columns in the existing dataset based upon certain conditions


data havem1;
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time : hhmmss5. DIAM	$ PXMC	$ SF;
format date date9. time hhmm5.;
cards;
7101 7101 US000521 1 Works1 08Nov2016 11:58	890,3 s1 1
7102 7102 US000361 1 Works2 02Nov2016 13:01	878,1 s2 1
7102 7102 UC000348 2 Works3 07Nov2016 18:22 877,3 s1 1
7106 7106 UC00424 1 Works1 05Oct2016 9:43 890,4 s1 1
7106 7106 UC00437 3 Works2 07Nov2016 18:23 877,1 s1 1
7106 7106 UC309	4 Works3 07Nov2016 18:26 877,8 s1 1
7107 7107 UC05327 1 Works1 06Oct2016 8:41 837 s1 1
7107 7107 UC200 2 Works2 13Oct2016 12:53 890,55 s1 1
7108 7108 UC000361 3 Works1	02Nov2016 13:01 878,1 s1 1
7108 7108 UC00432 1 Works2 07Nov2016 18:25 877,8 s1 1
7108 7108 UC106 2 Works3 03Oct2016 9:37	890,3 s1 1
;
run;

data wantm1;
 set havem1;
 array x{1} _temporary_;array z{1} _temporary_;
 array y{1}  _temporary_;
 x{1}=date;z{1}=SF;y{1}=TPMC;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 SF_before=0;
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 SF_before=.;
 SF=z{1};
 set havem1 point=_n_;output;
 SF=.;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 SF_after=1;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;






use function  BA_SF=COALESCE(SF,SF_before,SF_after) to get BA_SF .

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: how can we add columns in the existing dataset based upon certain conditions

[ Edited ]

I note you have several question on the same topic on here.  The process is the same for all, and you can appraoch it multiple ways but they require the same knowledge of the data that you should have:

Add any additional records you want (set using another data, or just add to the end of a datastep)

Use conditional output statements in a datastep

Both of these require that you know where the data is to go, what is the defing logic to where the records should go.  From what you have posted, the simplest method would be:

data want;
  set d1 d2;
run;
proc sort data=want;
  by tpmc date time;
run;

D1 being the data you have, and D2 being the additional data to add in.

 

To note, it does sound like your overcomplicating all these things.

Frequent Contributor
Posts: 87

Re: how can we add columns in the existing dataset based upon certain conditions

Yes I agree that I posted several questions. But this how I will learn complicated processes in SAS. I already told that I started learning SAS some one month ago. Sorry if i had irritated community. Should I stop posting? In that case many of my doubts will remain unsolved and, unfortunately, doing so might ask me to move to some other statistical package Smiley Sad
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: how can we add columns in the existing dataset based upon certain conditions

Nope, no irritation or anything Smiley Happy.  It just seems like your overcomplicating your problem.  There are many ways to approach any problem.  99% of programming is in the documentation - plan you code, plan your testing, plan your code lifecycle.  These things will help you locigallly think about the problem.  Too often programmers get stuck in the midst of their code and can't see the big picture.  

So as mentioned above, identify the "logical" postion of the data, from what you post it is sorted by date time, so you don't need to insert the data at that place, you can set datasets together and then sort them.  This is a key general skill, adding data together and then sorting it.

 

Solution
‎11-21-2016 11:18 AM
Grand Advisor
Posts: 9,571

Re: how can we add columns in the existing dataset based upon certain conditions


data havem1;
infile cards truncover expandtabs;
input TPMC	PWC	PWSC $	Site	ET	$ Date : date9.	Time : hhmmss5. DIAM	$ PXMC	$ SF;
format date date9. time hhmm5.;
cards;
7101 7101 US000521 1 Works1 08Nov2016 11:58	890,3 s1 1
7102 7102 US000361 1 Works2 02Nov2016 13:01	878,1 s2 1
7102 7102 UC000348 2 Works3 07Nov2016 18:22 877,3 s1 1
7106 7106 UC00424 1 Works1 05Oct2016 9:43 890,4 s1 1
7106 7106 UC00437 3 Works2 07Nov2016 18:23 877,1 s1 1
7106 7106 UC309	4 Works3 07Nov2016 18:26 877,8 s1 1
7107 7107 UC05327 1 Works1 06Oct2016 8:41 837 s1 1
7107 7107 UC200 2 Works2 13Oct2016 12:53 890,55 s1 1
7108 7108 UC000361 3 Works1	02Nov2016 13:01 878,1 s1 1
7108 7108 UC00432 1 Works2 07Nov2016 18:25 877,8 s1 1
7108 7108 UC106 2 Works3 03Oct2016 9:37	890,3 s1 1
;
run;

data wantm1;
 set havem1;
 array x{1} _temporary_;array z{1} _temporary_;
 array y{1}  _temporary_;
 x{1}=date;z{1}=SF;y{1}=TPMC;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 SF_before=0;
 do i=5 to 1 by -1;
   date=x{1}-i;output;
 end;
 SF_before=.;
 SF=z{1};
 set havem1 point=_n_;output;
 SF=.;
 call missing(of _all_);
 TPMC=y{1};Time=0;
 SF_after=1;
 do i=1 to 5;
   date=x{1}+i;output;
 end;
 drop i;
 run;






use function  BA_SF=COALESCE(SF,SF_before,SF_after) to get BA_SF .
Frequent Contributor
Posts: 87

Re: how can we add columns in the existing dataset based upon certain conditions

Thank you so much. Now I understand the logic of coding as above. Smiley Happy
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 215 views
  • 2 likes
  • 3 in conversation