culliso3 Tracker
https://communities.sas.com/kntur85557/tracker
culliso3 TrackerSun, 13 Oct 2024 21:11:06 GMT2024-10-13T21:11:06ZHow to use PROC SQL value to create new variable?
https://communities.sas.com/t5/SAS-Programming/How-to-use-PROC-SQL-value-to-create-new-variable/m-p/833714#M329596
<P>Hello community,</P><P> </P><P>I'm trying to utilize PROC SQL to count the frequency of a variable and then use the count from a different row to create a new variable.</P><P> </P><P>An example of my data:</P><P> </P><TABLE border="1"><TBODY><TR><TD>Worker ID</TD><TD>Facility</TD><TD>Year</TD></TR><TR><TD>1</TD><TD>A</TD><TD><P>2010</P></TD></TR><TR><TD>2</TD><TD>A</TD><TD>2010</TD></TR><TR><TD>3</TD><TD>A</TD><TD>2011</TD></TR><TR><TD>4</TD><TD>A</TD><TD>2012</TD></TR><TR><TD>5</TD><TD>A</TD><TD>2012</TD></TR><TR><TD>6</TD><TD>B</TD><TD>2011</TD></TR><TR><TD>7</TD><TD>B</TD><TD>2011</TD></TR><TR><TD>8</TD><TD>B</TD><TD>2012</TD></TR><TR><TD>9</TD><TD>B</TD><TD>2012</TD></TR><TR><TD>10</TD><TD>B</TD><TD>2013</TD></TR></TBODY></TABLE><P> </P><P>What I want to accomplish in PROC SQL:</P><P> </P><TABLE border="1"><TBODY><TR><TD>Facility</TD><TD>Year</TD><TD>Count</TD><TD>Count from Previous Year</TD></TR><TR><TD>A</TD><TD>2010</TD><TD>2</TD><TD>0</TD></TR><TR><TD>A</TD><TD>2011</TD><TD>1</TD><TD>2</TD></TR><TR><TD>A</TD><TD>2012</TD><TD>2</TD><TD>1</TD></TR><TR><TD>A</TD><TD>2013</TD><TD>0</TD><TD>2</TD></TR><TR><TD>B</TD><TD>2010</TD><TD>0</TD><TD>0</TD></TR><TR><TD>B</TD><TD>2011</TD><TD>2</TD><TD>0</TD></TR><TR><TD>B</TD><TD>2012</TD><TD>2</TD><TD>2</TD></TR><TR><TD>B</TD><TD>2013</TD><TD>1</TD><TD>2</TD></TR></TBODY></TABLE><P>I know how to get to the "Count" variable using Group by, however getting the "Count from Previous Year" is where I'm having difficulty.</P><P> </P><P>And then ultimately I want to use the "Count from previous year" variable and add it on to the original table by facility and year, so:</P><P> </P><TABLE border="1"><TBODY><TR><TD>Worker ID</TD><TD>Facility</TD><TD>Year</TD><TD>Count from Previous Year</TD></TR><TR><TD>1</TD><TD>A</TD><TD><P>2010</P></TD><TD><P>0</P></TD></TR><TR><TD>2</TD><TD>A</TD><TD>2010</TD><TD>0</TD></TR><TR><TD>3</TD><TD>A</TD><TD>2011</TD><TD>2</TD></TR><TR><TD>4</TD><TD>A</TD><TD>2012</TD><TD>1</TD></TR><TR><TD>5</TD><TD>A</TD><TD>2012</TD><TD>1</TD></TR><TR><TD>6</TD><TD>B</TD><TD>2011</TD><TD>0</TD></TR><TR><TD>7</TD><TD>B</TD><TD>2011</TD><TD>0</TD></TR><TR><TD>8</TD><TD>B</TD><TD>2012</TD><TD>2</TD></TR><TR><TD>9</TD><TD>B</TD><TD>2012</TD><TD>2</TD></TR><TR><TD>10</TD><TD>B</TD><TD>2013</TD><TD>2</TD></TR></TBODY></TABLE><P> </P><P>I'm open to any and all suggestions! Thank you all in advance.</P>Thu, 15 Sep 2022 19:47:09 GMThttps://communities.sas.com/t5/SAS-Programming/How-to-use-PROC-SQL-value-to-create-new-variable/m-p/833714#M329596culliso32022-09-15T19:47:09ZRe: How to create a table with nrows from frequency?
https://communities.sas.com/t5/SAS-Programming/How-to-create-a-table-with-nrows-from-frequency/m-p/731361#M227818
<P>Wow, I was way overthinking this. Thank you! </P>Mon, 05 Apr 2021 13:55:04 GMThttps://communities.sas.com/t5/SAS-Programming/How-to-create-a-table-with-nrows-from-frequency/m-p/731361#M227818culliso32021-04-05T13:55:04ZHow to create a table with nrows from frequency?
https://communities.sas.com/t5/SAS-Programming/How-to-create-a-table-with-nrows-from-frequency/m-p/731356#M227815
<P>Feel free to direct my to an article or post if this has been discussed previously.</P><P> </P><P>I'd like to create a table that replicates an observation based on a frequency count. While I'm able to use the frequency (or weight) command for many functions, it would be nice to have a full cross-tabulation of the data as well. A sample of my data would be:</P><P> </P><P>Have:</P><TABLE border="1"><TBODY><TR><TD>Group</TD><TD>Event</TD><TD>Study</TD><TD>Count</TD></TR><TR><TD>1</TD><TD>1</TD><TD>1</TD><TD>3</TD></TR><TR><TD>1</TD><TD>0</TD><TD>1</TD><TD>2</TD></TR><TR><TD>0</TD><TD>1</TD><TD>1</TD><TD>4</TD></TR></TBODY></TABLE><P> </P><P>Want:</P><P> </P><TABLE border="1"><TBODY><TR><TD>Group</TD><TD>Event</TD><TD>Study</TD></TR><TR><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>1</TD><TD>1</TD><TD>1</TD></TR><TR><TD>1</TD><TD>0</TD><TD>1</TD></TR><TR><TD>1</TD><TD>0</TD><TD>1</TD></TR><TR><TD>0</TD><TD>1</TD><TD>1</TD></TR><TR><TD>0</TD><TD>1</TD><TD>1</TD></TR><TR><TD>0</TD><TD>1</TD><TD>1</TD></TR><TR><TD>0</TD><TD>1</TD><TD>1</TD></TR></TBODY></TABLE><P> </P><P>I used low frequencies here but my frequencies are actually in the hundreds or thousands. My inkling is that I should be using an array within a data step but I haven't figured it out yet.</P><P> </P><P>I appreciate any answers! I'm keen to learn different potential approaches as well. </P>Mon, 05 Apr 2021 13:42:41 GMThttps://communities.sas.com/t5/SAS-Programming/How-to-create-a-table-with-nrows-from-frequency/m-p/731356#M227815culliso32021-04-05T13:42:41ZRe: How do I ensure GENMOD will output results?
https://communities.sas.com/t5/Statistical-Procedures/How-do-I-ensure-GENMOD-will-output-results/m-p/693604#M33474
<P>Thank you for your reply ballardw,</P><P>I agree it is curious as to why they didn't include the strata or cluster elements, although the paragraph below output 2 does acknowledge their importance for calculation accuracy.</P><P> </P><P>The documentation of the data set describes the sampling as follows:</P><P>The universe is separated into strata. Strata are determined upon 5 variables. Multiple individual clusters belong within each strata. Each cluster represents a hospital. Sampling of data from each cluster is based on "a<SPAN> probability sample of all clusters within a frame, with sampling probabilities proportional to the number of clusters in each stratum".</SPAN></P><P> </P><P>My REPEATED statement is structured such that each cluster [Hosp_nis] is nested within each stratum [Nis_stratum], which although I may certainly be incorrect, is structured suitably to account for this sampling design. </P><P> </P><P> </P>Thu, 22 Oct 2020 19:32:40 GMThttps://communities.sas.com/t5/Statistical-Procedures/How-do-I-ensure-GENMOD-will-output-results/m-p/693604#M33474culliso32020-10-22T19:32:40ZRe: How do I ensure GENMOD will output results?
https://communities.sas.com/t5/Statistical-Procedures/How-do-I-ensure-GENMOD-will-output-results/m-p/693578#M33469
<P>Thank you for this prompt reply. </P><P>I am basing my analysis off of this paper which describes the use of GENMOD for survey-weighted data. </P><P><A href="https://support.sas.com/resources/papers/proceedings13/272-2013.pdf" target="_blank">https://support.sas.com/resources/papers/proceedings13/272-2013.pdf</A></P><P> </P><P>The intent of using GENDMOD and GEE is also to explore how to data compares using an exchangeable correlation structure compared to an identity structure. Should the results be similar I will likely perform a final analysis in SURVEYLOGISTIC.</P><P> </P><P>I appreciate any further thoughts or suggestions.</P>Thu, 22 Oct 2020 18:03:59 GMThttps://communities.sas.com/t5/Statistical-Procedures/How-do-I-ensure-GENMOD-will-output-results/m-p/693578#M33469culliso32020-10-22T18:03:59ZHow do I ensure GENMOD will output results?
https://communities.sas.com/t5/Statistical-Procedures/How-do-I-ensure-GENMOD-will-output-results/m-p/693565#M33464
<P>Background:</P><P>I am using GENMOD to run a GEE on survey weighted data. My code below includes the correlated aspect of my data which is hospital #. I am using a national dataset, and the total observations are ~54 million and ~50 variables (I cut out many already but I realize I should've cut out all but the ones absolutely needed)</P><PRE><CODE class=" language-sas">ods graphics off;
ods exclude all;
ods results off;
sasfile work.finalscabies open;<BR />
proc genmod data=finalscabies;
class hosp_nis Nis_stratum race homeless female agecat pay1 zipinc_qrtl / descending;
model scabies(event='1')= Race female AgeCat Pay1 homeless ZIPINC_QRTL / dist=bin link=logit maxiter=10;
weight discwt;
repeated subject=Hosp_Nis(nis_stratum) / TYPE=EXCH;
estimate 'Black' race 0 0 0 0 1 -1 /exp;
estimate 'Hispanic' race 0 0 0 1 0 -1 /exp;
estimate 'API' race 0 0 1 0 0 -1 /exp;
estimate 'NA' race 0 1 0 0 0 -1 /exp;
lsmeans homeless / OR cl;
lsmeans female / OR cl;
lsmeans agecat / OR cl;
lsmeans pay1 / OR diff=all cl;
lsmeans ZIPINC_QRTL / OR diff=all cl;
ods output Estimates=estimateESTscabies GEEEmpPEst=GEEest GEEFitCritera=GEEFit LSMeans=OR1 Diffs=ORDiffs;
run;
ods exclude none;
sasfile work.finalscabies close;</CODE></PRE><P>Dilemma:</P><P>I ran the GEE without any of the estimate statements and without the SASFILE line to load it to memory. This took 1 hour to run and output.</P><P>I added 4 estimate statements and ran the code and it took 33 hours. Unfortunately I still had tweaks to make. I added the lines lsmeans lines, changed the ods settings to hopefully increase performance and ran the code again. This took 48 hours, at which point my computer did an automatic update without me realizing and all was lost. </P><P>Finally, I added the SASFILE line to load this massive file (24 GB) to memory of which I have 40GB of RAM. </P><P> </P><P>I ran the code for the 3rd time and it's currently running at 36 hours elapsed. Here's where the real question comes in. The log only shows:</P><PRE><CODE class=" language-sas">NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 ods graphics off;
29 ods exclude all
30 ods results off;</CODE></PRE><P>In prior runs of the code, the log would show:</P><P>"NOTE: Algorithm Converged" after approximately 15-20 minutes.</P><P>What is my log reflecting? Surely it must be running the GENMOD after 36 hours? </P><P> </P><P>Additional Information:</P><P>Looking at my RAM usage I saw an initial increase to 28GB used early on in the run. It's now down to 18GB use. </P><P> </P><P>Thank you for anyone who can provide advice, suggestions, or an answer!! </P><P>(Yes I will remove the extra variables if for some reason I have to run this again)</P>Thu, 22 Oct 2020 17:25:14 GMThttps://communities.sas.com/t5/Statistical-Procedures/How-do-I-ensure-GENMOD-will-output-results/m-p/693565#M33464culliso32020-10-22T17:25:14ZRe: Change Scatter Plot Axis Values?
https://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/648398#M19906
<P>Thank you! This was exactly the issue. A line of data was provided in the source data for "overall" which used a value of "." under Age_Adjusted_Rate. This caused SAS to recognize that variable type as character rather than number. Deleting this line of data fixed my issue.</P><P> </P>Sun, 17 May 2020 13:44:40 GMThttps://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/648398#M19906culliso32020-05-17T13:44:40ZRe: Change Scatter Plot Axis Values?
https://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/648396#M19905
<P>You were exactly right. For some reason the "Age_Adjusted_Rate" variable type is character rather than number. I downloaded a similar data set from the same source and ran the proc contents and got that the variable is number. When running the scatter plot for this data it worked perfectly. </P><P> </P><P>Opening the excel spreadsheet I found the website source includes a line of "overall" data. Deleting this line and re-running allowed SAS to recognize the variables as number rather than character. </P><P> </P><P>Thank you so much for your help!</P>Sun, 17 May 2020 13:40:14 GMThttps://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/648396#M19905culliso32020-05-17T13:40:14ZChange Scatter Plot Axis Values?
https://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/647642#M19882
<P>I've look at several guides across the internet and can't figure this out so I'm posting here.</P><P> </P><P>I'm creating a basic scatter plot, however the ticks are discrete rather than continuous, and I can find no way to change this.</P><P> </P><P>The first set of code produces the graph below: </P><PRE><CODE class=" language-sas">proc sgplot data=accidents noautolegend;
scatter x=year y=age_adjusted_rate /markerattrs=(color=red symbol=circlefilled);
label age_adjusted_rate="Car Accident Death Rate";
run;</CODE></PRE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="SGPlot15.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/39393i2A1DB91F877877E2/image-size/medium?v=v2&px=400" role="button" title="SGPlot15.png" alt="SGPlot15.png" /></span></P><P>As you can see, SAS is automatically using the discrete values from the data for both the X and Y axes rather than continuous integers. Also why would SAS put the Y axis in descending order?!?</P><P> </P><P>I changed the code to the following:</P><PRE><CODE class=" language-sas">proc sgplot data=accidents noautolegend;
scatter x=year y=age_adjusted_rate /markerattrs=(color=red symbol=circlefilled);
label age_adjusted_rate="Car Accident Death Rate";
xaxis grid values=(1999 to 2016 by 5);
yaxis grid values=(2 to 8 by 1);
run;</CODE></PRE><P>Unfortunately while this changed my axes to the values I want, SAS is still using discrete values. This code produces the graph below. </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="SGPlot21.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/39394i478FFB71DB7905B7/image-size/medium?v=v2&px=400" role="button" title="SGPlot21.png" alt="SGPlot21.png" /></span></P><P>How do I fix this? I tried playing around in ODS Graphic Designer forever and couldn't find any options. </P>Wed, 13 May 2020 21:57:05 GMThttps://communities.sas.com/t5/Graphics-Programming/Change-Scatter-Plot-Axis-Values/m-p/647642#M19882culliso32020-05-13T21:57:05ZHow to Generate Random Number Data Sets and Display Set Furthest From Normal Distribution?
https://communities.sas.com/t5/SAS-Programming/How-to-Generate-Random-Number-Data-Sets-and-Display-Set-Furthest/m-p/646717#M193492
<P>I'll preface by saying I am a complete novice to SAS so may need more in depth explanations than other users.</P><P> </P><P>I'm wanting to generate a cluster of data sets (say 50) comprised of 75 rolls of two dice. Then with these 50 data sets, I want to have SAS find the data set that least resembles a normal distribution. I want to view that seeds set of 75 rolls as a PROC Freq table. I have the code to randomize 75 dice rolls (I'll paste below in case you want to make changes) but have no clue where to start with coding for a cluster of data 50 data sets, and what function may exist to have SAS identify the data set that is furthest from a normal distribution.</P><P> </P><P>Thanks for any help!</P><P> </P><P> </P><PRE><CODE class=" language-sas">DATA DICE(KEEP=SUM) OUTCOMES(KEEP=OUTCOME);
DO ROLL=1 TO 75;
OUTCOME1=1+INT(6*RANUNI(123));
OUTCOME2=1+INT(6*RANUNI(123));
SUM=OUTCOME+OUTCOME2;
OUTPUT DICE;
OUTCOME=OUTCOME1; OUTPUT OUTCOMES;
OUTCOME=OUTCOME2; OUTPUT OUTCOMES;
END;
RUN;
PROC FREQ DATA=DICE;
TABLE SUM;
RUN;
PROC FREQ DATA=OUTCOMES;
TABLE OUTCOME;
RUN;</CODE></PRE>Mon, 11 May 2020 14:28:14 GMThttps://communities.sas.com/t5/SAS-Programming/How-to-Generate-Random-Number-Data-Sets-and-Display-Set-Furthest/m-p/646717#M193492culliso32020-05-11T14:28:14Z