DATA Step, Macro, Functions and more

PROC TRANSPOSE with conditions

Reply
Contributor
Posts: 35

PROC TRANSPOSE with conditions

Dear all,

 

I have been breaking my head over the below logic. Kindly help:

 

Have data set: 

 

ID Status Code System Name Value Rule_Label
123XXX Active BDD EXX City C07 City: Numeric,City: Special Characters
123XXX Active CDD ABB City C07 City: Numeric,City: Special Characters
123XXX Active ADD CAA City - City: Special Characters
123XXX Active ADD CAA City   Occupation: Blank
456XXX Inactive ADD CAA State   State: Blank
456XXX Inactive ADD CAA Code 111 mismatch
456XXX Active CDD CAA Code \M blank
456XXX Active CDD CAA City c13 City: Numeric,City: Special Characters
789XXX Inactive CDD ABB Code N41 City: Numeric,City: Special Characters
910XXX Active MMM ABB City 8 City: Numeric,City: Special Characters
910XXX Active MMM EXX City - City: Special Characters
910XXX Active MMM EXX State   State: Blank

Need data set (attached

please let me know the logic

Super User
Posts: 7,866

Re: PROC TRANSPOSE with conditions

Please don't post data in Excel format files. Many firewalls prevent the downloading of such files, and sensible people don't open Office files from the web, anyway, because of security reasons.

Use the {i} subwindow to post fixed-font text.

At least use the same method you used for the "have" data.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 35

Re: PROC TRANSPOSE with conditions

Posted in reply to KurtBremser

Hi Kurt,

Thank you for the advise. Noted and Thank you!

 

Have data :

ID Status Code System Name Value Rule_Label
123XXX Active BDD EXX City C07 City: Numeric,City: Special Characters
123XXX Active CDD ABB City C07 City: Numeric,City: Special Characters
123XXX Active ADD CAA City - City: Special Characters
123XXX Active ADD CAA City   Occupation: Blank
456XXX Inactive ADD CAA State   State: Blank
456XXX Inactive ADD CAA Code 111 mismatch
456XXX Active CDD CAA Code \M blank
456XXX Active CDD CAA City c13 City: Numeric,City: Special Characters
789XXX Inactive CDD ABB Code N41 City: Numeric,City: Special Characters
910XXX Active MMM ABB City 8 City: Numeric,City: Special Characters

 

Needed data set:

 

ID Status Code EXX   ABB   CAA   EXX   ABB   CAA   EXX   CAA   ABB
Code Rule Label Code Rule Label Code Rule Label City Rule Label City Rule Label City Rule Label State Rule Label State Rule Label State
123XXX                                      
456XXX                                      
456XXX                                      
456XXX                                      
456XXX                                      
789XXX                                      
910XXX                                      
910XXX                                      

 

Please help..

Super User
Posts: 7,866

Re: PROC TRANSPOSE with conditions

Seems to me that proc transpose will not be able to handle this, so you will have to do it in a data step.

Some questions:

Do you know beforehand how many distinct values for "system" and "name" are present, or will that change over time?

And why is there only one line for 123XXX, but four lines for 456XXX?

What happens with the original column "value"?

Which original value for "code" should be preserved in the case of 123XXX?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 35

Re: PROC TRANSPOSE with conditions

Posted in reply to KurtBremser
Hi Kurt,
1. My bad, the needed data set should not be having dup IDs, for got to filter them.
2. column value values should be comnng unser EXX-CODE (if ID is 123XXX, system is EXXX with name CODE then what is the "Value" for that..based on the above have table, there is not "system" - EXX with "Name" CODE hence the value is null.
3. in case of 123XXX the original value for CODE shd be "BDD" (code need to place a null in case of no matches)

Thank you very much!
Super User
Posts: 7,866

Re: PROC TRANSPOSE with conditions

I still don't completely understand your requirements, but my basic approach would be:

- sort dataset byID

- In a data step:

- use by ID;

- define the new variables (LENGTH, FORMAT) and RETAIN them

- with if first.ID, set the variables to missing values

- then, set variables for each record, as per requirements

- with if last.id then output; write one record per ID

 

If the content of the selection variables is not pre-determined, you will have to wrap everything into a macro that first determines all possible values, saves them into lists, and then create the code dynamically from those lists.

But first I'd construct the data step with given values to make sure the code works as desired.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 497 views
  • 1 like
  • 2 in conversation