Dear SAS users,
I have two columns with values separated by comma as below:
column 1
row1: HB,SF,IVF,SVF,HB,SF,SVF,SF,SVF,HB,SF,HB,SVF
row2: IVF,SF,HB,SF,SVF
row3: SF,HB,IVF
row3: SF,HB,IVF
column 2
row1: 0.0,1,2,0,0,0,,1,0,0,0,2,1
row2: 1,2,0,0,0
row3: 1,1,0
row4: 1,0,0
Where column 1 contains the code of a lab test: there are 4 type: HB, SF, SVF, and IVF and only IVF have only 1 test, the 3 others will have multiple tests
And column 2 contains the test results: 0 =negative, 1=positive, 2=ambiguous, blank=missing results
The test and the result will exactly order the same, but the tests are not in any sequential.
I need to create 2 variables to show the test results using those 2 columns. The first variable is to show to test result of all tests that happened before IVF test. The second variables is to show the test result after IVF test.
I want,
in row 1, there are 2 tests before IVF, and they are all negative so the VAR1=0. There are 10 tests after IVF and have at least 1 test=1 so VAR2=1.
In row 2, there is no test before IVF, so VAR1=null. There are 4 tests after IVF and none=1 but one test=2 so VAR2=2;
in row 3, there are 2 tests before IVF and both=1 so VAR1=1 and VAR2=blank (no test after IVF)
in row 4, there are 2 tests before IVF and only one test=1 so VAR1=0 and VAR2=blank (no test after IVF)
what SAS procedure should be the best to handle this. I tried scan, array, countw but none seems working properly.
Any help from you all will be highly appreciated.
Best,
BL
... View more
