topic Re: creation of new records in SAS Programming

creation of new records

Bhargav_Movva — Thu, 15 Nov 2018 17:16:10 GMT

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

Re: creation of new records

Reeza — Thu, 15 Nov 2018 17:19:48 GMT

Post the exact output you want.

Re: creation of new records

PeterClemmensen — Thu, 15 Nov 2018 17:21:53 GMT

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

Re: creation of new records

Bhargav_Movva — Thu, 15 Nov 2018 17:26:17 GMT

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

Re: creation of new records

PeterClemmensen — Thu, 15 Nov 2018 17:29:02 GMT

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.

Re: creation of new records

Bhargav_Movva — Thu, 15 Nov 2018 17:30:03 GMT

Yes i want it in a dataset

Re: creation of new records

Reeza — Thu, 15 Nov 2018 17:32:49 GMT

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.

Re: creation of new records

Bhargav_Movva — Thu, 15 Nov 2018 17:35:01 GMT

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 .

Re: creation of new records

Reeza — Thu, 15 Nov 2018 17:47:52 GMT

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;

Re: creation of new records

Bhargav_Movva — Thu, 15 Nov 2018 17:52:35 GMT

i couldnt understand your logic

Re: creation of new records

Reeza — Thu, 15 Nov 2018 17:54:53 GMT

Which step?

Re: creation of new records

Bhargav_Movva — Thu, 15 Nov 2018 18:21:14 GMT

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

Re: creation of new records

SuryaKiran — Thu, 15 Nov 2018 18:43:13 GMT

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;