Sunboyss30 Tracker
https://communities.sas.com/kntur85557/tracker
Sunboyss30 TrackerWed, 09 Oct 2024 17:17:44 GMT2024-10-09T17:17:44ZRe: Subsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801256#M315330
<P>Hi <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/35763">@yabwon</a> Thankyou for your reply. Yes its perfect and that's what i was looking for. Really appreciate.</P>Thu, 10 Mar 2022 07:57:04 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801256#M315330Sunboyss302022-03-10T07:57:04ZRe: Subsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801094#M315254
<P>Thankyou <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/281770">@maguiremq</a> its almost there and i am getting the results as expected with one last thing that in your answer if the code in ('A1','B1') but here i am looking for all the codes between A1-A7 and B1-B7. Since the code is doing seq increment on the basis of previous code not matching. So if i replace the code with below snippet i am only getting one record for the id = 5 whereas it should have all 6 records since the code for that id has A1-A3 which can be upto A7. Apologies if I haven't explained it earlier.</P>
<LI-CODE lang="sas">if code in ('A1','A2','A3','A4','A5','A6','A7','B1','B2','B3','B4','B5','B6','B7')
then flag = 1;</LI-CODE>
<P> </P>Wed, 09 Mar 2022 15:25:43 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801094#M315254Sunboyss302022-03-09T15:25:43ZRe: Subsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801076#M315242
Yes in the chronologically order. Meaning if i have any code other than A1-A7 and B1-B7 in between any 6 month cycle. Then only keep the observations per group for the latest months .Wed, 09 Mar 2022 14:07:18 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801076#M315242Sunboyss302022-03-09T14:07:18ZRe: Subsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801074#M315240
<P>Thankyou for your reply <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/10892">@PaigeMiller</a> . i have tried to add more explanation to the above.</P>Wed, 09 Mar 2022 13:54:22 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801074#M315240Sunboyss302022-03-09T13:54:22ZRe: Subsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801070#M315237
<P>Thankyou for your reply <a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/281770">@maguiremq</a> .Yes the additional patterns can be between (A1 to A7) and (B1 to B7) and i need all of them except if they break in between for example in ID = 1 it had a different code (Z1 & X1) in Sep and Oct in between and i dont need the observations prior to that even if its one of the codes i am looking for. So i cant just simply filter out on 'B1' as a group can have all A1 or B1 codes and that ways i will have extra observations not required. Please see the code i have added 2 more ids to explain.</P>
<P><BR /><BR />data have;<BR />infile datalines delimiter = ' ';<BR />input id date :date9. code :$2. flag cnt;<BR />format date date9.;<BR />datalines;<BR />1 30NOV2021 B1 1 1<BR />1 31OCT2021 X1 0 0<BR />1 30SEP2021 Z1 0 0<BR />1 31AUG2021 A1 1 1<BR />1 31JUL2021 A1 1 2<BR />1 30JUN2021 A1 1 3<BR />2 30NOV2021 B1 1 1<BR />2 31OCT2021 B1 1 2<BR />2 30SEP2021 B1 1 3<BR />2 31AUG2021 Z1 0 0<BR />2 31JUL2021 A1 1 1<BR />2 30JUN2021 A1 1 2<BR />3 30NOV2021 B1 1 1<BR />3 31OCT2021 B1 1 2<BR />3 30SEP2021 Z1 0 0<BR />3 31AUG2021 Z1 0 0<BR />3 31JUL2021 A1 1 1<BR />3 30JUN2021 A1 1 2<BR />4 30NOV2021 B1 1 1<BR />4 31OCT2021 B1 1 2<BR />4 30SEP2021 B1 1 3<BR />4 31AUG2021 B1 1 4<BR />4 31JUL2021 B1 1 5<BR />4 30JUN2021 B1 1 6<BR />5 30NOV2021 A3 1 1<BR />5 31OCT2021 A2 1 2<BR />5 30SEP2021 A1 1 3<BR />5 31AUG2021 A1 1 4<BR />5 31JUL2021 A1 1 5<BR />5 30JUN2021 A1 1 6<BR />;<BR />run;</P>
<P> </P>
<P>If i just filter on the required codes will give extra observations and i need only the ones highlighted.</P>
<TABLE width="274">
<TBODY>
<TR>
<TD width="50">id</TD>
<TD width="74">date</TD>
<TD width="50">code</TD>
<TD width="50">flag</TD>
<TD width="50">cnt</TD>
</TR>
<TR>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>30Nov2021</STRONG></TD>
<TD><STRONG>B1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
</TR>
<TR>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>31Aug2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
</TR>
<TR>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>31Jul2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>2</SPAN></TD>
</TR>
<TR>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>30Jun2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>3</SPAN></TD>
</TR>
<TR>
<TD><STRONG>2</STRONG></TD>
<TD><STRONG>30Nov2021</STRONG></TD>
<TD><STRONG>B1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
</TR>
<TR>
<TD><STRONG>2</STRONG></TD>
<TD><STRONG>31Oct2021</STRONG></TD>
<TD><STRONG>B1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>2</STRONG></TD>
</TR>
<TR>
<TD><STRONG>2</STRONG></TD>
<TD><STRONG>30Sep2021</STRONG></TD>
<TD><STRONG>B1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>3</STRONG></TD>
</TR>
<TR>
<TD><SPAN>2</SPAN></TD>
<TD><SPAN>31Jul2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
</TR>
<TR>
<TD><SPAN>2</SPAN></TD>
<TD><SPAN>30Jun2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>2</SPAN></TD>
</TR>
<TR>
<TD><STRONG>3</STRONG></TD>
<TD><STRONG>30Nov2021</STRONG></TD>
<TD><STRONG>B1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
</TR>
<TR>
<TD><STRONG>3</STRONG></TD>
<TD><STRONG>31Oct2021</STRONG></TD>
<TD><STRONG>B1</STRONG></TD>
<TD><STRONG>1</STRONG></TD>
<TD><STRONG>2</STRONG></TD>
</TR>
<TR>
<TD><SPAN>3</SPAN></TD>
<TD><SPAN>31Jul2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
</TR>
<TR>
<TD><SPAN>3</SPAN></TD>
<TD><SPAN>30Jun2021</SPAN></TD>
<TD><SPAN>A1</SPAN></TD>
<TD><SPAN>1</SPAN></TD>
<TD><SPAN>2</SPAN></TD>
</TR>
</TBODY>
</TABLE>Wed, 09 Mar 2022 13:49:57 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801070#M315237Sunboyss302022-03-09T13:49:57ZRe: Subsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801059#M315231
i was able to create a flag and counter (cnt) for the observations but i need only the ones in green.<BR /><BR />Wed, 09 Mar 2022 12:52:21 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801059#M315231Sunboyss302022-03-09T12:52:21ZSubsetting in a group of values in sas
https://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801058#M315230
<P>Hello members,</P>
<P> </P>
<P>I am trying to filter out multiple rows from a group of observation where it matches to my need. So i have a dataset where each id will have 6 observations with different codes and the observations i need has specific codes for example A1 and B1 but from these codes also i need observations which are populated after any other code from the above two. Below is the quick snapshot of the dummy data and i need all the observations highlighted in green. Please let me know if i am unable to post my question properly or more details required.</P>
<P> </P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Sunboyss30_0-1646833707031.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/69280i05D171A18A6B003C/image-size/medium?v=v2&px=400" role="button" title="Sunboyss30_0-1646833707031.png" alt="Sunboyss30_0-1646833707031.png" /></span></P>
<P> </P>
<P> </P>
<P> </P>
<P> </P>Wed, 09 Mar 2022 13:48:39 GMThttps://communities.sas.com/t5/SAS-Programming/Subsetting-in-a-group-of-values-in-sas/m-p/801058#M315230Sunboyss302022-03-09T13:48:39ZRe: Summarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635295#M30423
Thanks a lot Kurt. its perfect but i also want to filter only group le 2 (having ABC + any other group value) AND group gt 2 (having ABC + any other group value) per ID. Right now group le 2 also has combination of DEF & GHI.<BR /><BR />Really appreciate.<BR />Fri, 27 Mar 2020 12:22:47 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635295#M30423Sunboyss302020-03-27T12:22:47ZRe: Summarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635241#M30414
Hi Paige,<BR />Thanks for your reply. Really appreciate.<BR />I need to count the number of ids and sum the values based on a combination of group. So scenario 1, let say group value ABC with only 1 other group value (any other than ABC) and count the ids and sum the variable. such cases.<BR />scenario 2 - Then group value ABC with more than 1 other group value (any other than ABC) and count the ids and sum the variable.<BR />So the output should be like ;<BR />scenario 1<BR />Group id count of group count of account sum_var<BR />ABC 5678 2 2 107985447.5<BR />JKL 5678 2 2 107985447.5<BR /><BR />scenario 2<BR />Group id count of group count of account sum_var<BR />ABC 1234 3 4 171119250.5<BR />DEF 1234 3 4 171119250.5<BR />DEF 1234 3 4 171119250.5<BR />GHI 1234 3 4 171119250.5<BR />Also, ids that have been summarized in scenario 1 should not be counted in scenario 2.<BR />Thanks in advanceFri, 27 Mar 2020 08:55:55 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635241#M30414Sunboyss302020-03-27T08:55:55ZRe: Summarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635236#M30413
Hi Tom,<BR />Thanks for your reply. Really appreciate.<BR />I need to count the number of ids and sum the values based on a combination of group. So scenario 1, let say group value ABC with only 1 other group value (any other than ABC) and count the ids and sum the variable. such cases.<BR />scenario 2 - Then group value ABC with more than 1 other group value (any other than ABC) and count the ids and sum the variable.<BR />So the output should be like ;<BR />scenario 1<BR />Group id count of group count of account sum_var<BR />ABC 5678 2 2 107985447.5<BR />JKL 5678 2 2 107985447.5<BR /><BR />scenario 2<BR />Group id count of group count of account sum_var<BR />ABC 1234 3 4 171119250.5<BR />DEF 1234 3 4 171119250.5<BR />DEF 1234 3 4 171119250.5<BR />GHI 1234 3 4 171119250.5<BR /><BR />Thanks in advanceFri, 27 Mar 2020 08:26:10 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635236#M30413Sunboyss302020-03-27T08:26:10ZRe: Summarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635133#M30411
Also to add, ids that have been summarized in scenario 1 should not be counted in scenario 2.Thu, 26 Mar 2020 19:41:22 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635133#M30411Sunboyss302020-03-26T19:41:22ZRe: Summarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635129#M30410
Hi Kurt,<BR />Thanks for your reply. Really appreciate.<BR /><BR />Actually you are correct. May be I was not clear in earlier.<BR />I need to count the number of ids and sum the values based on a combination of group. So scenario 1, let say group value ABC with only 1 other group value (any other than ABC) and count the ids and sum the variable. such cases.<BR />scenario 2 - Then group value ABC with more than 1 other group value (any other than ABC) and count the ids and sum the variable.<BR />So the output should be like ;<BR />scenario 1<BR />Group id count of group count of account sum_var<BR />ABC 5678 2 2 107985447.5<BR />JKL 5678 2 2 107985447.5<BR /><BR />scenario 2<BR />Group id count of group count of account sum_var<BR />ABC 1234 3 4 171119250.5<BR />DEF 1234 3 4 171119250.5<BR />DEF 1234 3 4 171119250.5<BR />GHI 1234 3 4 171119250.5<BR /><BR />Thanks in advanceThu, 26 Mar 2020 19:29:32 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635129#M30410Sunboyss302020-03-26T19:29:32ZRe: Summarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635032#M30403
Hi Paige,<BR /><BR />Thanks for your reply. The data set is big. Please find below an example of the same.<BR />There are 9 such groups in the actual data and in the example below i have taken only 5.<BR />So, if you see id 1234 is present in three different groups.<BR />I want to count in how many other group that id is other then ABC with that group name and also sum the<BR />field sum_Var?<BR /><BR />Group Account sum_var id<BR />ABC 10792402 53475319.06 1234<BR />DEF 3928847912 4510128.39 1234<BR />DEF 4682433039 89231128.39 1234<BR />GHI 1737576722 23902674.65 1234<BR />ABC 43169608 53475319.06 5678<BR />JKL 15715391648 54510128.39 5678Thu, 26 Mar 2020 12:58:15 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635032#M30403Sunboyss302020-03-26T12:58:15ZSummarize data based on different group combination
https://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635013#M30401
<P>Hello community,</P><P> </P><P>I want to summarize the data based on id's present in multiple groups and see whats there count in combination of group. For example; Below are the counts in each group and based on the ids i want to check the counts in other groups as well. So if one id is present in one group, it can be in other group as well. I am trying achieving with case statements but is there any procedure and or data step i can use to get the results. I hope i made myself clear.</P><P> </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Group</TD><TD>id Counts</TD></TR><TR><TD>abc</TD><TD>10603</TD></TR><TR><TD>def</TD><TD>84780</TD></TR><TR><TD>ghi</TD><TD>249401</TD></TR><TR><TD>jkl</TD><TD>184111</TD></TR><TR><TD>mno</TD><TD>77921</TD></TR><TR><TD>pqr</TD><TD>843570</TD></TR></TBODY></TABLE>Thu, 26 Mar 2020 11:53:14 GMThttps://communities.sas.com/t5/Statistical-Procedures/Summarize-data-based-on-different-group-combination/m-p/635013#M30401Sunboyss302020-03-26T11:53:14ZRe: ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by th
https://communities.sas.com/t5/New-SAS-User/ERROR-At-least-one-of-the-columns-in-this-DBMS-table-has-a/m-p/610971#M18168
I agree. Thanks a lot for your inputs.Wed, 11 Dec 2019 13:53:05 GMThttps://communities.sas.com/t5/New-SAS-User/ERROR-At-least-one-of-the-columns-in-this-DBMS-table-has-a/m-p/610971#M18168Sunboyss302019-12-11T13:53:05ZRe: ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by th
https://communities.sas.com/t5/New-SAS-User/ERROR-At-least-one-of-the-columns-in-this-DBMS-table-has-a/m-p/610633#M18131
<P>Hi <SPAN>tomrvincent,</SPAN></P><P> </P><P><SPAN>Thanks for your reply. I agree with you but I need to consider all the fields in that table and if a pick it instead of * it makes the code go very long. And i was just trying avoiding that.</SPAN></P>Tue, 10 Dec 2019 07:18:50 GMThttps://communities.sas.com/t5/New-SAS-User/ERROR-At-least-one-of-the-columns-in-this-DBMS-table-has-a/m-p/610633#M18131Sunboyss302019-12-10T07:18:50ZERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this e
https://communities.sas.com/t5/New-SAS-User/ERROR-At-least-one-of-the-columns-in-this-DBMS-table-has-a/m-p/610436#M18104
<P>Hi ,</P><P> </P><P>When i am trying to fetch data from Teradata into SAS with a select * statement , I get the below error. I know it has to do with the format as SAS <SPAN>does not support the Teradata data types. But when I select all the fields with their names instead of * it doesn't give error. Please suggest.</SPAN></P><P> </P><P><SPAN>ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.</SPAN></P>Mon, 09 Dec 2019 11:31:46 GMThttps://communities.sas.com/t5/New-SAS-User/ERROR-At-least-one-of-the-columns-in-this-DBMS-table-has-a/m-p/610436#M18104Sunboyss302019-12-09T11:31:46ZRe: proc compare - Output difference with padded zero values
https://communities.sas.com/t5/SAS-Procedures/proc-compare-Output-difference-with-padded-zero-values/m-p/416299#M67612
<P>Hi,</P><P> </P><P>Thanks for your reply. Actually the datasets are coming from two different databases Oracle and Teradata. And what if I dont want to change the format in the source datasets. Any other way of doing it.</P><P> </P><P>Regards,</P>Mon, 27 Nov 2017 09:44:08 GMThttps://communities.sas.com/t5/SAS-Procedures/proc-compare-Output-difference-with-padded-zero-values/m-p/416299#M67612Sunboyss302017-11-27T09:44:08Zproc compare - Output difference with padded zero values
https://communities.sas.com/t5/SAS-Procedures/proc-compare-Output-difference-with-padded-zero-values/m-p/415822#M67583
<P>Hi,</P><P> </P><P>I am trying to compare two datasets in sas using proc compare. One of the character variable is not matching and shows difference however when i actual see the record the values are correct. They are defined as below in both base and comparing data set.</P><P> </P><P>Base dataset --VARIABLE1 ;TYPE-Char Len-16 Format-$16. Inf<SPAN>ormat-$16.</SPAN></P><P> </P><P><SPAN>Comp dataset --VARIABLE1 ;TYPE-Char Len-23 Format-$23. Informat-$23.</SPAN></P><P> </P><P><SPAN>Also, attached is the output for couple of records.</SPAN></P><P> </P><P><SPAN>Thank in advance.</SPAN></P><P> </P><P><SPAN>Regards,</SPAN></P>Thu, 23 Nov 2017 13:30:14 GMThttps://communities.sas.com/t5/SAS-Procedures/proc-compare-Output-difference-with-padded-zero-values/m-p/415822#M67583Sunboyss302017-11-23T13:30:14ZRe: Proc compare showing matching values even after using OUTNOEQUAL Option
https://communities.sas.com/t5/SAS-Procedures/Proc-compare-showing-matching-values-even-after-using-OUTNOEQUAL/m-p/413983#M67478
<P>Hi,</P><P> </P><P>Also to check for Date values. I have two variables defined as Date9. and Datetime20. which obviously give difference. But is there any way to match these. Do i need to convert the format of any of the variable to match it. Or any option in Proc compare is there?</P><P> </P><P>Please suggest.</P><P> </P><P>Regards,</P>Thu, 16 Nov 2017 13:17:01 GMThttps://communities.sas.com/t5/SAS-Procedures/Proc-compare-showing-matching-values-even-after-using-OUTNOEQUAL/m-p/413983#M67478Sunboyss302017-11-16T13:17:01Z