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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

imanojkumar1
Quartz | Level 8
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 😞
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Ksharp
Super User

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 .
imanojkumar1
Quartz | Level 8
Thank you so much. Now I understand the logic of coding as above. 🙂

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1114 views
  • 2 likes
  • 3 in conversation