Comparing the rows and acheive the desired output.

Reply
Occasional Contributor
Posts: 5

Comparing the rows and acheive the desired output.

Hi,


I am enclosing the requirements for my request. Also I am attaching the sample data as well as the output(Which I am trying to get) attached.

1.      Read first record in file (or last record read)

2.      Read next record:
     CDA_NBR of next record = CDA_NBR of previous record
     or

     SPLIT of next record  = null

     or
     (SPLIT of previous record  = null and SPLIT of next record = 0.0,

     and the previous record is not the first record in the grouping of SEG_NUM, TYPE, SD_TYPE)

then create new record. See xlsx tab for specification of new record.

Else, read next record (or back to step 2 above)

3.      End of data

Please let me know how to solve this. Please let me know if you any more information. Ideas are appreciated.

Super User
Posts: 7,429

Re: Comparing the rows and acheive the desired output.

You can use the lag() function to access values of previous records.

Using "by" processing (appropriate sorting of data necessary), you can detect the start of a group with first.variable.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,867

Re: Comparing the rows and acheive the desired output.

0000-0012 is not satisfied with your condition. Why it would be appear at the output ?

Occasional Contributor
Posts: 5

Re: Comparing the rows and acheive the desired output.

Hi All,

Thanks for the responses. Appreciate your help. The requirement is like this Xia Keshan. Even I am having hard time achieving this.

Occasional Contributor
Posts: 5

Re: Comparing the rows and acheive the desired output.

More Explanation:

As per the second condition it will compare the previous row with the current and creates a new record, in the CDA_NBR variable it will check the 5 observation and compares with 4 because they are equal it will create new record.

Here is what I have tried from my side and let me know where to make the change to reflect the output(shown in sheet2 of excel).

PROC SORT DATA=have OUT=need;

BY ID;

RUN;

DATA need123 (DROP=_Smiley Happy;

MERGE

need(FIRSTOBS=2 KEEP=CAD_NBR SPLIT RENAME=(CAD_NBR=_CAD_NBR SPLIT=_SPLIT))

need;

OUTPUT;

IF CAD_NBR =_CAD_NBR

  or _SPLIT  =  null

  or (SPLIT  =  null and _SPLIT = 0.00000)

   THEN DO;

CALL MISSING(OF _ALL_);

OUTPUT;

END;

RUN;

Please try to help on this. Also would request you to post the code.

Ask a Question
Discussion stats
  • 4 replies
  • 297 views
  • 0 likes
  • 3 in conversation