About d0816

d0816 · ‎11-24-2020

@novinosrin I hit the first one as solution when I intended to select this updated one as solution. Sorry about that.

d0816 · ‎11-24-2020

@mkeintz Thank you. Appreciate your help. This code also works.

d0816 · ‎11-24-2020

@r_behata This code also worked. Thank you. Appreciate your help.

d0816 · ‎11-24-2020

@novinosrin Thank you. Appreciate your help. Code worked well.

d0816 · ‎11-23-2020

Hi, I have this this dataset. For each "Time" category, for ID with multiple rows, I want to collapse it into one row per ID. Group category is seven types and I don't anticipate this max number to change. I also want to put the groups in one column and in alphabetical order separated by / and also want to count the number of groups for each ID in a separate column. How can I achieve this? Thank you for your help. ID Time Group Salary_Q1 Salary_Q2 1 2020-Q1 GGGGGGG $295.00 $88.00 2 2020-Q1 AA $1,918.00 $2,309.00 3 2020-Q1 BB $0.00 $0.00 4 2020-Q1 CCC $0.00 $0.00 5 2020-Q1 EEE $2,900.00 $2,466.00 6 2020-Q1 DD $0.00 $0.00 7 2020-Q1 FF $0.00 $0.00 8 2020-Q1 EEE $0.00 $0.00 8 2020-Q1 AA $0.00 $0.00 9 2020-Q1 BB $0.00 $0.00 9 2020-Q1 CCC $0.00 $0.00 10 2020-Q1 CCC $0.00 $0.00 10 2020-Q1 AA $0.00 $0.00 11 2020-Q1 BB $3,964.00 $2,886.00 11 2020-Q1 DD $3,964.00 $2,886.00 12 2020-Q1 GGGGGGG $2,351.00 $7,101.00 12 2020-Q1 AA $2,351.00 $7,101.00 13 2020-Q1 GGGGGGG $0.00 $550.00 13 2020-Q1 EEE $0.00 $550.00 13 2020-Q1 AA $0.00 $550.00 14 2020-Q1 GGGGGGG $3,796.00 $6,055.00 14 2020-Q1 AA $3,796.00 $6,055.00 14 2020-Q1 BB $3,796.00 $6,055.00 14 2020-Q1 CCC $3,796.00 $6,055.00 14 2020-Q1 EEE $3,796.00 $6,055.00 14 2020-Q1 DD $3,796.00 $6,055.00 14 2020-Q1 FF $3,796.00 $6,055.00 12 2020-Q2 GGGGGGG $2,351.00 $7,101.00 15 2020-Q2 CCC $0.00 $0.00 15 2020-Q2 AA $0.00 $0.00 13 2020-Q2 BB $0.00 $550.00 13 2020-Q2 CCC $0.00 $550.00 16 2020-Q2 GGGGGGG $2,224.00 $0.00 16 2020-Q2 AA $2,224.00 $0.00 16 2020-Q2 BB $2,224.00 $0.00 16 2020-Q2 CCC $2,224.00 $0.00 16 2020-Q2 EEE $2,224.00 $0.00 16 2020-Q2 DD $2,224.00 $0.00 16 2020-Q2 FF $2,224.00 $0.00 Dataset I want: ID Time Group Salary_Q1 Salary_Q2 No.of Groups 1 2020-Q1 GGGGGGG $295.00 $88.00 1 2 2020-Q1 AA $1,918.00 $2,309.00 1 3 2020-Q1 BB $0.00 $0.00 1 4 2020-Q1 CCC $0.00 $0.00 1 5 2020-Q1 EEE $2,900.00 $2,466.00 1 6 2020-Q1 DD $0.00 $0.00 1 7 2020-Q1 FF $0.00 $0.00 1 8 2020-Q1 AA/EEE $0.00 $0.00 2 9 2020-Q1 BB/CCC $0.00 $0.00 2 10 2020-Q1 AA/CCC $0.00 $0.00 2 11 2020-Q1 BB/DD $3,964.00 $2,886.00 2 12 2020-Q1 AA/GGGGGGG $2,351.00 $7,101.00 2 13 2020-Q1 AA/EEE/GGGGGGG $0.00 $550.00 3 14 2020-Q1 AA/BB/CCC/DD/EEE/FF/GGGGGGG $3,796.00 $6,055.00 7 12 2020-Q2 GGGGGGG $2,351.00 $7,101.00 1 15 2020-Q2 AA/CCC $0.00 $0.00 2 13 2020-Q2 BB/CCC $0.00 $550.00 2 16 2020-Q2 AA/BB/CCC/DD/EEE/FF/GGGGGGG $2,224.00 $0.00 7

d0816 · ‎03-06-2020

@ballardw I ran this code... filename tagset url "https://gist.githubusercontent.com/statgeek/7be124b98b37d51e7c002b85b6e9cf72/raw/ec0bc5cf8e1a3386c5cf71d2280b242b7f87dadd/SAS_tagsets_sql_reprex.sas"; %include tagset; ods tagsets.sql file="H:\Desktop\FolderName/Sample1.txt"; proc print data=Sample1 ; run; ods tagsets.sql close; ...and got this error message... ERROR: Cannot load SSL support. ERROR: Cannot open %INCLUDE file TAGSET. ERROR: %INCLUDE has encountered an I/O/ error. Canceling submitted statements

d0816 · ‎03-05-2020

@PaigeMiller I am not being able to generate the sas data set following the method. I tried the codes including the edits. I am getting errors.

d0816 · ‎03-05-2020

I am provided excel file in a wide-form and I am importing it to sas. So I am stuck with it. so, 01. Q3 1996 in excel becomes _01__Q3_1996 in sas. First Event Begin Quarter, Last Event Begin QTR, Last Event End QTR got imported as Type Char, Length 7, Format $7., and Informat $5. . _01___Q3_1996 and most of the columns got imported as Type Char, Length 5, Format $5., and Informat $5. .Some columns got imported as Type Num, Length 8, Format NLMNY15.2, and no Informat.

d0816 · ‎03-05-2020

@PaigeMiller yes you are right. I gave you excel column names instead of sas column names. clearly I was not thinking. To give you the context, I have a dataset like below. ID First Event Begin Quarter Last Event Begin QTR Last Event End QTR _01__Q3_1996 _02__Q4_1996 _03__Q1_1997 _04__Q2_1997 _05__Q3_1997 _06__Q4_1997 _07__Q1_1998 _08__Q2_1998 _09__Q3_1998 _10__Q4_1998 _11__Q1_1999 _12__Q2_1999 _13__Q3_1999 _14__Q4_1999 _15__Q1_2000 _16__Q2_2000 _17__Q3_2000 _18__Q4_2000 _19__Q1_2001 _20__Q2_2001 _21__Q3_2001 _22__Q4_2001 _23__Q1_2002 _24__Q2_2002 _25__Q3_2002 _26__Q4_2002 _27__Q1_2003 _28__Q2_2003 _29__Q3_2003 _30__Q4_2003 _31__Q1_2004 _32__Q2_2004 _33__Q3_2004 _34__Q4_2004 _35__Q1_2005 _36__Q2_2005 _37__Q3_2005 _38__Q4_2005 _39__Q1_2006 _40__Q2_2006 _41__Q3_2006 _42__Q4_2006 _43__Q1_2007 _44__Q2_2007 _45__Q3_2007 _46__Q4_2007 _47__Q1_2008 _48__Q2_2008 _49__Q3_2008 _50__Q4_2008 _51__Q1_2009 _52__Q2_2009 _53__Q3_2009 _54__Q4_2009 _55__Q1_2010 _56__Q2_2010 _57__Q3_2010 _58__Q4_2010 _59__Q1_2011 _60__Q2_2011 _61__Q3_2011 _62__Q4_2011 _63__Q1_2012 _64__Q2_2012 _65__Q3_2012 _66__Q4_2012 _67__Q1_2013 _68__Q2_2013 _69__Q3_2013 _70__Q4_2013 _71__Q1_2014 _72__Q2_2014 _73__Q3_2014 _74__Q4_2014 _75__Q1_2015 _76__Q2_2015 _77__Q3_2015 _78__Q4_2015 _79__Q1_2016 _80__Q2_2016 _82__Q4_2016 _83__Q1_2017 _84__Q2_2017 _85__Q3_2017 _86__Q4_2017 _87__Q1_2018 _88__Q3_2016 _89__Q2_2018 _90__Q3_2018 _91__Q4_2018 _92__Q1_2019 _93__Q2_2019 1 1998-Q1 2017-Q4 2018-Q2 $0.00 $220.00 $0.00 $0.00 $132.00 $138.00 $0.00 $487.00 $0.00 $0.00 $0.00 $0.00 $0.00 $919.00 $0.00 $0.00 $634.00 $557.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $739.00 $180.00 $0.00 $0.00 $529.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $33.00 $0.00 $5,577.00 $5,577.00 $5,467.00 $3,959.00 $3,698.00 2 2001-Q3 2017-Q4 2018-Q2 $0.00 $67.00 $150.00 $0.00 $0.00 $696.00 $0.00 $0.00 $1,007.00 $1,307.00 $2,824.00 $103.00 $1,918.00 $2,150.00 $1,229.00 $86.00 $0.00 $0.00 $1,179.00 $1,629.00 $2,679.00 $2,659.00 $551.00 $1,550.00 $3,552.00 $909.00 $894.00 $3,099.00 $2,087.00 $3,648.00 $878.00 $1,388.00 $2,450.00 $2,613.00 $694.00 $0.00 $0.00 $1,494.00 $3.00 $300.00 $1,088.00 $2,171.00 $688.00 $4,193.00 $3,856.00 $4,393.00 $4,744.00 $4,271.00 $3,654.00 $4,407.00 $4,468.00 $4,449.00 $1,849.00 $2,746.00 $2,852.00 $2,415.00 $1,956.00 $1,027.00 $0.00 $3,141.00 $3,987.00 $3,775.00 $0.00 $1,619.00 $3,053.00 $3,230.00 $2,009.00 $4,508.00 $6,575.00 $8,747.00 $6,005.00 $4,886.00 $4,382.00 $3,898.00 $3,481.00 $1,636.00 $2,343.00 $5,689.00 $3,519.00 $3,539.00 $4,791.00 $3,635.00 $3,968.00 $3,686.00 $0.00 $6,687.00 $3,877.00 $8,302.00 $8,302.00 $7,478.00 $15.00 $5,605.00 3 1996-Q1 2018-Q1 2018-Q2 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $534.00 $1,623.00 $0.00 $0.00 $0.00 $0.00 $121.00 $912.00 $0.00 $0.00 $392.00 $387.00 $0.00 $1,749.00 $312.00 $2,186.00 $2,211.00 $1,503.00 $0.00 $627.00 $2,013.00 $1,582.00 $1,146.00 $974.00 $2,438.00 $1,493.00 $3,083.00 $4,418.00 $2,753.00 $2,652.00 $4,118.00 $3,849.00 $2,573.00 $3,030.00 $3,291.00 $2,925.00 $3,519.00 $2,358.00 $2,340.00 $4,104.00 $3,618.00 $4,212.00 $1,112.00 $3,962.00 $3,031.00 $4,057.00 $361.00 $0.00 $0.00 $957.00 $3,360.00 $1,620.00 $0.00 $2,347.00 $3,332.00 $3,817.00 $553.00 $478.00 $2,929.00 $3,419.00 $2,310.00 $2,671.00 $2,866.00 $3,456.00 $3,224.00 $2,951.00 $2,927.00 $2,566.00 $0.00 $0.00 $3,241.00 $0.00 $2,228.00 $2,956.00 $3,619.00 $3,619.00 $3,788.00 $2,962.00 $4,186.00 I need to add three columns like below where I need to match the quarter with the column quarter and pick that value in that matching column quarter like below. If there is no matching column quarter I need to identify that and so put -$1.00 (no value is actual dataset is negative). I thought I will start with making the column names same but clearly I was not thinking. Amount in First Begin QTR Amount in Last Begin QTR Amount in Last End QTR $0.00 $0.00 $5,577.00 $2,679.00 $0.00 $8,302.00 -$1.00 $2,228.00 $3,619.00 How can I add these three columns? Thank you.

d0816 · ‎03-05-2020

Hi, I need help renaming column names. I have around 100 columns like this... 79. Q1 2016 80. Q2 2016 88. Q3 2016 82. Q4 2016 83. Q1 2017 84. Q2 2017 85. Q3 2017 86. Q4 2017 87. Q1 2018 89. Q2 2018 90. Q3 2018 91. Q4 2018 ..which I want to change to.. 2016-Q1 2016-Q2 2016-Q3 2016-Q4 2017-Q1 2017-Q2 2017-Q3 2017-Q4 2018-Q1 2018-Q2 2018-Q3 2018-Q4 I want to remove the preceding number (which btw may not be in order) and format the quarter as shown above. How can I do this? Appreciate any help. Thanks.

d0816 · ‎11-04-2019

Yes @Reeza, please move the post. Thanks.

d0816 · ‎11-01-2019

@ballardw It does not need to be a VA solution. I found a code that is trying to do similar thing except dont include data points in the chart but this example code do not give the said result. http://support.sas.com/kb/47/076.html Thank you.

d0816 · ‎11-01-2019

I found this resource, but the sample code in the sample dataset do not produce the result. Also it has min and max points but not all data points. http://support.sas.com/kb/24/871.html

d0816 · ‎11-01-2019

Hello, I would like to create a graph like below with mean and SD by category AND to this graph I would like to add actual data points( which was used to calculate means and SD) in each category. for e.g if 5 data points were used to calculate mean for category AAA I would like to show those 5 points as dots vertically for a category. Can you please suggest how to do this? Appreciate any help.Thank you.

d0816 · ‎09-20-2019

@PGStats @hashman and @FreelanceReinh, Thank you all so much for responding. The codes by @PGStats and @hashman both worked in the sample dataset I provided. But two codes returned me with different number of observations in my real dataset. I compared the results of two codes in my real dataset and looked for IDs with different no. of records. Like @FreelanceReinh said, my sample dataset may not have contained all possible cases even though I strived for it. So I created new sample dataset (see below) from original dataset. Data step code is returning the result as intended. It looks like SQL code is picking most recent StartDate only for an ID. I will try the fix @FreelanceReinh suggested. Thanks. data have_new ; input id startdate :mmddyy10. (startmonth yearmonth) (:$7.) ; format startdate yymmdd10. ; cards ; 100 5/19/2019 2019-05 2019-06 100 5/19/2019 2019-05 2019-07 100 5/19/2019 2019-05 2019-08 100 5/19/2019 2019-05 2019-09 100 7/27/2019 2019-07 2019-06 100 7/27/2019 2019-07 2019-07 100 7/27/2019 2019-07 2019-08 100 7/27/2019 2019-07 2019-09 200 5/6/2019 2019-05 2019-06 200 5/6/2019 2019-05 2019-07 200 5/6/2019 2019-05 2019-08 200 5/6/2019 2019-05 2019-09 200 6/6/2019 2019-06 2019-06 200 6/6/2019 2019-06 2019-07 200 6/6/2019 2019-06 2019-08 200 6/6/2019 2019-06 2019-09 200 7/17/2019 2019-07 2019-06 200 7/17/2019 2019-07 2019-07 200 7/17/2019 2019-07 2019-08 200 7/17/2019 2019-07 2019-09 300 4/15/2019 2019-04 2019-07 300 4/15/2019 2019-04 2019-08 300 4/15/2019 2019-04 2019-09 300 7/24/2019 2019-07 2019-07 300 7/24/2019 2019-07 2019-08 300 7/24/2019 2019-07 2019-09 400 5/7/2019 2019-05 2019-06 400 5/7/2019 2019-05 2019-07 400 5/7/2019 2019-05 2019-08 400 5/7/2019 2019-05 2019-09 400 7/25/2019 2019-07 2019-06 400 7/25/2019 2019-07 2019-07 400 7/25/2019 2019-07 2019-08 400 7/25/2019 2019-07 2019-09 500 4/15/2019 2019-04 2019-05 500 4/15/2019 2019-04 2019-06 500 4/15/2019 2019-04 2019-07 500 4/15/2019 2019-04 2019-08 500 4/15/2019 2019-04 2019-09 500 6/12/2019 2019-06 2019-05 500 6/12/2019 2019-06 2019-06 500 6/12/2019 2019-06 2019-07 500 6/12/2019 2019-06 2019-08 500 6/12/2019 2019-06 2019-09 600 4/1/2019 2019-04 2019-07 600 4/1/2019 2019-04 2019-08 600 4/1/2019 2019-04 2019-09 600 7/29/2019 2019-07 2019-07 600 7/29/2019 2019-07 2019-08 600 7/29/2019 2019-07 2019-09 700 3/15/2019 2019-03 2019-05 700 3/15/2019 2019-03 2019-06 700 3/15/2019 2019-03 2019-07 700 3/15/2019 2019-03 2019-08 700 3/15/2019 2019-03 2019-09 700 6/14/2019 2019-06 2019-05 700 6/14/2019 2019-06 2019-06 700 6/14/2019 2019-06 2019-07 700 6/14/2019 2019-06 2019-08 700 6/14/2019 2019-06 2019-09 800 4/17/2019 2019-04 2019-07 800 4/17/2019 2019-04 2019-08 800 4/17/2019 2019-04 2019-09 800 7/10/2019 2019-07 2019-07 800 7/10/2019 2019-07 2019-08 800 7/10/2019 2019-07 2019-09 900 5/21/2019 2019-05 2019-07 900 5/21/2019 2019-05 2019-08 900 5/21/2019 2019-05 2019-09 900 7/31/2019 2019-07 2019-07 900 7/31/2019 2019-07 2019-08 900 7/31/2019 2019-07 2019-09 1000 6/17/2019 2019-06 2019-07 1000 6/17/2019 2019-06 2019-08 1000 6/17/2019 2019-06 2019-09 1000 7/12/2019 2019-07 2019-07 1000 7/12/2019 2019-07 2019-08 1000 7/12/2019 2019-07 2019-09 2000 3/12/2019 2019-03 2019-07 2000 3/12/2019 2019-03 2019-08 2000 3/12/2019 2019-03 2019-09 2000 7/29/2019 2019-07 2019-07 2000 7/29/2019 2019-07 2019-08 2000 7/29/2019 2019-07 2019-09 3000 3/18/2019 2019-03 2019-04 3000 3/18/2019 2019-03 2019-05 3000 3/18/2019 2019-03 2019-06 3000 3/18/2019 2019-03 2019-07 3000 3/18/2019 2019-03 2019-08 3000 3/18/2019 2019-03 2019-09 3000 5/9/2019 2019-05 2019-04 3000 5/9/2019 2019-05 2019-05 3000 5/9/2019 2019-05 2019-06 3000 5/9/2019 2019-05 2019-07 3000 5/9/2019 2019-05 2019-08 3000 5/9/2019 2019-05 2019-09 4000 3/23/2019 2019-03 2019-06 4000 3/23/2019 2019-03 2019-07 4000 3/23/2019 2019-03 2019-08 4000 3/23/2019 2019-03 2019-09 4000 7/13/2019 2019-07 2019-06 4000 7/13/2019 2019-07 2019-07 4000 7/13/2019 2019-07 2019-08 4000 7/13/2019 2019-07 2019-09 5000 4/1/2019 2019-04 2019-05 5000 4/1/2019 2019-04 2019-06 5000 4/1/2019 2019-04 2019-07 5000 4/1/2019 2019-04 2019-08 5000 4/1/2019 2019-04 2019-09 5000 6/26/2019 2019-06 2019-05 5000 6/26/2019 2019-06 2019-06 5000 6/26/2019 2019-06 2019-07 5000 6/26/2019 2019-06 2019-08 5000 6/26/2019 2019-06 2019-09 6000 3/23/2019 2019-03 2019-06 6000 3/23/2019 2019-03 2019-07 6000 3/23/2019 2019-03 2019-08 6000 3/23/2019 2019-03 2019-09 6000 4/16/2019 2019-04 2019-06 6000 4/16/2019 2019-04 2019-07 6000 4/16/2019 2019-04 2019-08 6000 4/16/2019 2019-04 2019-09 6000 7/2/2019 2019-07 2019-06 6000 7/2/2019 2019-07 2019-07 6000 7/2/2019 2019-07 2019-08 6000 7/2/2019 2019-07 2019-09 7000 3/8/2019 2019-03 2019-05 7000 3/8/2019 2019-03 2019-06 7000 3/8/2019 2019-03 2019-07 7000 3/8/2019 2019-03 2019-08 7000 3/8/2019 2019-03 2019-09 7000 7/2/2019 2019-07 2019-05 7000 7/2/2019 2019-07 2019-06 7000 7/2/2019 2019-07 2019-07 7000 7/2/2019 2019-07 2019-08 7000 7/2/2019 2019-07 2019-09 ; run;

Online Status	Offline
Date Last Visited	‎11-24-2020 01:22 PM

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Multiple row per ID to one row per ID

Re: rename column

Re: rename column

Re: rename column

Re: rename column

rename column

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: spliting a dataset based on conditions

Re: subset a dataset based on variable obs of another dataset

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Re: Multiple row per ID to one row per ID

Multiple row per ID to one row per ID

Re: rename column

Re: rename column

Re: rename column

Re: rename column

rename column

Re: create bar chart of mean/SD and datapoints

Re: create bar chart of mean/SD and datapoints

Re: create bar chart of mean/SD and datapoints

create bar chart of mean/SD and include datapoints (dont have to be Vi...

Re: Keep record based on condition