Logic Behind this Data Structure
in [SAS]
|
From: Al on 23 Jun 2010 13:18 Dear all : This is th data i have data have ; input pat $ Dep $ rel $ out $ ds $ ; cards; 123 CLI MIL RES AE 123 CLI MOD RES DS 345 DMA SEV NOT AE 345 DMA SEV RES DS 436 TES SEV RRR DS 555 PRA SEV NOT DS 555 PRA MON RES AE ; run; I am trying to get to this data set. If there is a mismatch between values of rel ,out per pat and per dep .. i need to create a variable flag . flag field will have the variable name where the mismatch has occured for example for pat 123 and Dep CLI : There was a mismatch in values of rel (MIL,MOD) .. so the flag value should be the variable name (rel) where the mismatch has occured for pat 345 and Dep DMA : There was a mismatch in values of out (NOT,RES) .. so the flag value should be the variable name (out)where the mismatch has occured for pat 436 and Dep PRA : for all unique values the value of flag should be All for pat 555 and Dep PRA : There was a mismatch in values of rel(SEV,MON) and out(NOT,RES) so the flag value should be both variable names separated by comma rel,out The flag value must be always in first.pat observation Desired output: pat Dep rel out ds flag 123 CLI MIL RES AE rel 123 CLI MOD RES DS 345 DMA SEV NOT AE out 345 DMA SEV RES DS 436 TES SEV RRR DS All 555 PRA SEV NOT DS rel,out 555 PRA MON RES AE How can i accomplish this .. Thanks in adavance Al
From: Ya on 23 Jun 2010 15:07 On Jun 23, 10:18 am, Al <ali6...(a)gmail.com> wrote: > Dear all : > > This is th data i have > > data have ; > input pat $ Dep $ rel $ out $ ds $ ; > cards; > 123 CLI MIL RES AE > 123 CLI MOD RES DS > 345 DMA SEV NOT AE > 345 DMA SEV RES DS > 436 TES SEV RRR DS > 555 PRA SEV NOT DS > 555 PRA MON RES AE > > ; > run; > > I am trying to get to this data set. If there is a mismatch between > values of rel ,out per pat and per dep .. i need to create a > variable > flag . flag field will have the variable name where the mismatch has > occured > > for example > > for pat 123 and Dep CLI : > There was a mismatch in values of rel (MIL,MOD) .. so the flag value > should be the variable name (rel) where the mismatch has occured > > for pat 345 and Dep DMA : > There was a mismatch in values of out (NOT,RES) .. so the flag value > should be the variable name (out)where the mismatch has occured > > for pat 436 and Dep PRA : > for all unique values the value of flag should be All > > for pat 555 and Dep PRA : > There was a mismatch in values of rel(SEV,MON) and out(NOT,RES) so the > flag value should be both variable names separated by comma > rel,out > > The flag value must be always in first.pat observation > > Desired output: > > pat Dep rel out ds flag > 123 CLI MIL RES AE rel > 123 CLI MOD RES DS > 345 DMA SEV NOT AE out > 345 DMA SEV RES DS > 436 TES SEV RRR DS All > 555 PRA SEV NOT DS rel,out > 555 PRA MON RES AE > > How can i accomplish this .. > > Thanks in adavance > Al proc sql; select *, case when count(distinct cats(dep,rel,out))=1 then 'all' when count(distinct dep)^=1 and count(distinct rel)^=1 and count(distinct out)^=1 then 'dep,rel,out' when count(distinct dep)^=1 and count(distinct rel)^=1 then 'dep,rel' when count(distinct dep)^=1 and count(distinct out)^=1 then 'dep,out' when count(distinct rel)^=1 and count(distinct out)^=1 then 'rel,out' when count(distinct dep)^=1 then 'dep' when count(distinct rel)^=1 then 'rel' when count(distinct out)^=1 then 'out' else '' end as flag from have group by pat ; pat Dep rel out ds flag ------------------------------------------------------------- 123 CLI MOD RES DS rel 123 CLI MIL RES AE rel 345 DMA SEV NOT AE out 345 DMA SEV RES DS out 436 TES SEV RRR DS all 555 PRA MON RES AE rel,out 555 PRA SEV NOT DS rel,out I think I've covered all Scenarios, you can try the real data and see if I missed any. HTH Ya
From: Al on 23 Jun 2010 15:41 On Jun 23, 2:07 pm, Ya <huang8...(a)gmail.com> wrote: > On Jun 23, 10:18 am, Al <ali6...(a)gmail.com> wrote: > > > > > > > Dear all : > > > This is th data i have > > > data have ; > > input pat $ Dep $ rel $ out $ ds $ ; > > cards; > > 123 CLI MIL RES AE > > 123 CLI MOD RES DS > > 345 DMA SEV NOT AE > > 345 DMA SEV RES DS > > 436 TES SEV RRR DS > > 555 PRA SEV NOT DS > > 555 PRA MON RES AE > > > ; > > run; > > > I am trying to get to this data set. If there is a mismatch between > > values of rel ,out per pat and per dep .. i need to create a > > variable > > flag . flag field will have the variable name where the mismatch has > > occured > > > for example > > > for pat 123 and Dep CLI : > > There was a mismatch in values of rel (MIL,MOD) .. so the flag value > > should be the variable name (rel) where the mismatch has occured > > > for pat 345 and Dep DMA : > > There was a mismatch in values of out (NOT,RES) .. so the flag value > > should be the variable name (out)where the mismatch has occured > > > for pat 436 and Dep PRA : > > for all unique values the value of flag should be All > > > for pat 555 and Dep PRA : > > There was a mismatch in values of rel(SEV,MON) and out(NOT,RES) so the > > flag value should be both variable names separated by comma > > rel,out > > > The flag value must be always in first.pat observation > > > Desired output: > > > pat Dep rel out ds flag > > 123 CLI MIL RES AE rel > > 123 CLI MOD RES DS > > 345 DMA SEV NOT AE out > > 345 DMA SEV RES DS > > 436 TES SEV RRR DS All > > 555 PRA SEV NOT DS rel,out > > 555 PRA MON RES AE > > > How can i accomplish this .. > > > Thanks in adavance > > Al > > proc sql; > select *, > case when count(distinct cats(dep,rel,out))=1 then 'all' > when count(distinct dep)^=1 and count(distinct rel)^=1 and > count(distinct out)^=1 then 'dep,rel,out' > when count(distinct dep)^=1 and count(distinct rel)^=1 then > 'dep,rel' > when count(distinct dep)^=1 and count(distinct out)^=1 then > 'dep,out' > when count(distinct rel)^=1 and count(distinct out)^=1 then > 'rel,out' > when count(distinct dep)^=1 then 'dep' > when count(distinct rel)^=1 then 'rel' > when count(distinct out)^=1 then 'out' > else '' end as flag > from have > group by pat > ; > > pat Dep rel out ds flag > ------------------------------------------------------------- > 123 CLI MOD RES DS rel > 123 CLI MIL RES AE rel > 345 DMA SEV NOT AE out > 345 DMA SEV RES DS out > 436 TES SEV RRR DS all > 555 PRA MON RES AE rel,out > 555 PRA SEV NOT DS rel,out > > I think I've covered all Scenarios, you can try the real data and see > if I missed any. > > HTH > > Ya- Hide quoted text - > > - Show quoted text - Works perfect .. i am also looking to leave the rel ,out values as missing if they match across the observation .by pat ,dep i.e since out values (RES )are same for pat 123 . they should be set to missing i.e since rel values (SEV )are same for pat 345 . they should be set to missing The output looks like below.i was able to get there using multiple steps which i dint like .. any efficient way to do this ... because in my real data i have about 10 variables pat Dep rel out ds flag > ------------------------------------------------------------- > 123 CLI MOD DS rel > 123 CLI MIL AE rel > 345 DMA SEV NOT AE out > 345 DMA RES DS out > 436 TES SEV RRR DS all > 555 PRA MON RES AE rel,out > 555 PRA SEV NOT DS rel,out and as always Thanks for your Valuable time Al
From: Ya on 23 Jun 2010 16:07 On Jun 23, 12:41 pm, Al <ali6...(a)gmail.com> wrote: > On Jun 23, 2:07 pm, Ya <huang8...(a)gmail.com> wrote: > > > > > > > On Jun 23, 10:18 am, Al <ali6...(a)gmail.com> wrote: > > > > Dear all : > > > > This is th data i have > > > > data have ; > > > input pat $ Dep $ rel $ out $ ds $ ; > > > cards; > > > 123 CLI MIL RES AE > > > 123 CLI MOD RES DS > > > 345 DMA SEV NOT AE > > > 345 DMA SEV RES DS > > > 436 TES SEV RRR DS > > > 555 PRA SEV NOT DS > > > 555 PRA MON RES AE > > > > ; > > > run; > > > > I am trying to get to this data set. If there is a mismatch between > > > values of rel ,out per pat and per dep .. i need to create a > > > variable > > > flag . flag field will have the variable name where the mismatch has > > > occured > > > > for example > > > > for pat 123 and Dep CLI : > > > There was a mismatch in values of rel (MIL,MOD) .. so the flag value > > > should be the variable name (rel) where the mismatch has occured > > > > for pat 345 and Dep DMA : > > > There was a mismatch in values of out (NOT,RES) .. so the flag value > > > should be the variable name (out)where the mismatch has occured > > > > for pat 436 and Dep PRA : > > > for all unique values the value of flag should be All > > > > for pat 555 and Dep PRA : > > > There was a mismatch in values of rel(SEV,MON) and out(NOT,RES) so the > > > flag value should be both variable names separated by comma > > > rel,out > > > > The flag value must be always in first.pat observation > > > > Desired output: > > > > pat Dep rel out ds flag > > > 123 CLI MIL RES AE rel > > > 123 CLI MOD RES DS > > > 345 DMA SEV NOT AE out > > > 345 DMA SEV RES DS > > > 436 TES SEV RRR DS All > > > 555 PRA SEV NOT DS rel,out > > > 555 PRA MON RES AE > > > > How can i accomplish this .. > > > > Thanks in adavance > > > Al > > > proc sql; > > select *, > > case when count(distinct cats(dep,rel,out))=1 then 'all' > > when count(distinct dep)^=1 and count(distinct rel)^=1 and > > count(distinct out)^=1 then 'dep,rel,out' > > when count(distinct dep)^=1 and count(distinct rel)^=1 then > > 'dep,rel' > > when count(distinct dep)^=1 and count(distinct out)^=1 then > > 'dep,out' > > when count(distinct rel)^=1 and count(distinct out)^=1 then > > 'rel,out' > > when count(distinct dep)^=1 then 'dep' > > when count(distinct rel)^=1 then 'rel' > > when count(distinct out)^=1 then 'out' > > else '' end as flag > > from have > > group by pat > > ; > > > pat Dep rel out ds flag > > ------------------------------------------------------------- > > 123 CLI MOD RES DS rel > > 123 CLI MIL RES AE rel > > 345 DMA SEV NOT AE out > > 345 DMA SEV RES DS out > > 436 TES SEV RRR DS all > > 555 PRA MON RES AE rel,out > > 555 PRA SEV NOT DS rel,out > > > I think I've covered all Scenarios, you can try the real data and see > > if I missed any. > > > HTH > > > Ya- Hide quoted text - > > > - Show quoted text - > > Works perfect .. i am also looking to leave the rel ,out values as > missing if they match across the observation .by pat ,dep > > i.e since out values (RES )are same for pat 123 . they should be set > to missing > > i.e since rel values (SEV )are same for pat 345 . they should be set > to missing > > The output looks like below.i was able to get there using multiple > steps which i dint like .. any efficient way to do this ... because in > my real data i have about 10 variables > > pat Dep rel out ds flag > > > ------------------------------------------------------------- > > 123 CLI MOD DS rel > > 123 CLI MIL AE rel > > 345 DMA SEV NOT AE out > > 345 DMA RES DS out > > 436 TES SEV RRR DS all > > 555 PRA MON RES AE rel,out > > 555 PRA SEV NOT DS rel,out > > and as always Thanks for your Valuable time > Al- Hide quoted text - > > - Show quoted text - Once you have the flagged dataset, you should be able to use another data step to reset the values.
|
Pages: 1 Prev: Problem with data import Next: Using # observations in conditional statements |