PROC TRANSPOSE with conditions

[don21]

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

[Kurt_Bremser]

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.

[don21]

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..

[Kurt_Bremser]

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?

[don21]

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!

[Kurt_Bremser]

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.