creation of new records

[Bhargav_Movva]

Hi , this is how my data is

subject   visit  result   tst

101         d1    20      EST

101         d2    30      EST

101         d3    40      EST

101         d4    50      EST

101         P1    40      EST

101         P2    50      EST

101         P3    60      EST

101         P4    70      EST

I want to create two new records and 4 new vars c1 ,c2, mc1, mc2

101       d-al    ---      ------

101       p-al    -----   -------

c1-- should be yes if result of all same letter visits are less than 40 ; mc1--should be yes if result of all same letter visits is missing

c2-should be yes if result of any same letter visits is less than 40 : mc2--should be yes if result of any same letter visits is missing

record d-al sholud be based on all d visits

recod p-al should be based on all p visits

[Reeza]

Post the exact output you want.

[PeterClemmensen]

So your desired output data set has only two rows, correct?

[Bhargav_Movva]

subject   visit  result   tst     c1     c2    mc1  mc2

101         d1    20      EST    -       -        -        -

101         d2    30      EST    -      -        -         -

101         d3    40      EST    -      -        -         -

101         d4    50      EST    -      -        -         -

101         P1    40      EST    -      -        -         -

101         P2    50      EST    -      -        -         -

101         P3    60      EST    -      -        -         -

101         P4    70      EST    -      -        -         -

101         d-a     -        Z       -      Y      Y        -

101         p-a    -          Z      Y     -       -         Y            

[PeterClemmensen]

Do you want this do be a SAS data set? It is rarely a good idea to have summary rows in data sets. They belong in reports.

[Bhargav_Movva]

Yes i want it in a dataset

[Reeza]

Will you have multiple ID or just a single as in your example? The better representative the sample is of your true data the more likely you are to get an answer that actually works for you.

[Bhargav_Movva]

yes i do have multiple id,i just quoted a piece of data as i cant represent all data here.Assume that there are multiple subjects and multiplle tst values like EST and PGT and TGT .

[Reeza]

1. Separate letter of visit out.
2. Add that as a BY variable in addition your ID (assumed).
3. add a flag variable that sets to 1 if any value is greater than 40.
4. If last.ID then you can use an explicit output.
output;
if last.id then do;
*check if flag is gerater than one here and assign desired values;
visit = 'd-al'; c1=;c2=; mc1=;mc2=;
output;
visit = 'P-al'; c1=;c2=; mc1=;mc2=;
output;
end;

[Bhargav_Movva]

i couldnt understand your logic

[Reeza]

Which step?

[Bhargav_Movva]

I have attached a text file and out should contain  ftwo more records for every subject,

with visit as d-AA p-AA

for d-aa row c1,c2,mc1,mc2 shoudl be populateda s

c1 should be Y if any of result value of all dvisits  is less than 40

c2- should be Y if all result value of dvisits are greater than 40

mc1-if any of result is missing for d visits then mc1 should be Y

mc2-if all results are missing for d visits then mc2 should be Y

same for p -aa row also

for paa row c1,c2,mc1,mc2 shoudl be populateda s

c1 should be Y if any of result value of all ovisits  is less than 40

c2- should be Y if all result value of pvisits are greater than 40

mc1-if any of result is missing for pvisits then mc1 should be Y

mc2-if all results are missing for pvisits then mc2 should be Y

so output should contain 2 records more per subject

[SuryaKiran]

Flag the result values into 3 categories, Y-less than 40 , N - greater than 40 and M missing values and then later read all the values for the group and searching the combined string would answer your question.

data have;
input subject visit:$3. result tst:$3.;
datalines;
101 d1 20 EST
101 d2 30 EST
101 d3 40 EST
101 d4 50 EST
101 P1 40 EST
101 P2 50 EST
101 P3 60 EST
101 P4 70 EST
;
run;
proc format ;
value flag low-<40='Y'
			   .='M'
			   other='N';
run;


data want_grp;
set have;
visit_grp=substr(visit,1,1);
result_Flag=put(result,flag.);
run;


proc sort data=want_grp;
by subject visit_grp;
data want;
format result_Flag_cat $15.;
do until (last.visit_grp);
set want_grp;
by subject visit_grp;
result_Flag_cat=cats(result_Flag_cat,result_Flag); /* All values in last record */
end;
/* If Y exists then atleast one values has less than 40 */
if find(result_Flag_cat,'Y')>0 then c1='Y' ; else c1='N';
/* If no Y or M then all values are greater than 40 */
if find(result_Flag_cat,'Y')=0 and find(result_Flag_cat,'M')=0    then c2='Y' ; else c2='N';
/* If atleast one values is missing */
if  find(result_Flag_cat,'M')>0 then mc1='Y'; else mc1='N';
/* If all results are missing, i.e. no Y or N */
if find(result_Flag_cat,'M')>0 and find(result_Flag_cat,'Y')=0 and find(result_Flag_cat,'N')=0  then mc2='Y'  ; else mc2='N';
run;