SAS Support Communities

ammarhm · ‎12-11-2022

Hi everyone, I am trying to recode columns in a table using references from another table. The first table, which basically contains coded diagnoses looks as follows: data Dx; input Person_ID Dx1_Ref Dx2_ref Dx3_Ref Dx4_Ref AdmissionSource DischargeDestination; datalines; 1 5 1 6 4 10 10 2 2 9 2 8 10 20 3 8 2 4 5 20 30 4 3 6 6 2 30 10 5 8 8 9 7 10 30 6 9 2 2 9 30 20 7 2 6 4 3 10 20 8 4 4 5 6 20 10 ; run; The second table which contains references to what the codes in the different columns above mean is this: data Code; length Column $20 Text $15; input Column $ Ref Text $; datalines; Dx1_Ref 1 HT Dx1_Ref 2 HC Dx1_Ref 3 DM Dx1_Ref 4 IHD Dx1_Ref 5 IB Dx1_Ref 6 CL Dx1_Ref 7 HF Dx1_Ref 8 DI Dx1_Ref 9 HG Dx2_Ref 1 HT Dx2_Ref 2 HC Dx2_Ref 3 DM Dx2_Ref 4 IHD Dx2_Ref 5 IB Dx2_Ref 6 CL Dx2_Ref 7 HF Dx2_Ref 8 DI Dx2_Ref 9 HG AdmissionSource 10 Home AdmissionSource 20 OtherHospital AdmissionSource 30 NursingHome DischargeDestination 10 Home DischargeDestination 20 OtherHospital DischargeDestination 30 NursingHome run; So I am generating a 'de-coded' table by extracting the text from the second table and matching it to the first one by column name and the value in the column: proc sql; create table Dx1 as select a.*, b.Text as Dx1_txt from Dx as a left join Code as b on a.Dx1_Ref=b.Ref and b.Column='Dx1_Ref' /*this is not very essential because 'Ref' in table Code is unique to the columns but would be good to be able to add*/ ; quit; And then I am repeating this process to get the text values for the remaining columns one by one: proc sql; create table Dx2 as select a.*, b.Text as Dx2_txt from Dx1 as a left join Code as b on a.Dx2_Ref=b.Ref and b.Column='Dx2_Ref' /*this is not very essential because 'Ref' in table Code is unique to the columns but would be good to be able to add*/ ; quit; and so forth for Dx3_ref, Dx4_ref, AdmissionSource, Discharge destination and so forth. This works just fine, but the actual tables and much bigger with many more columns, and it does not feel like an efficient way to solve this problem. I was just wondering if anyone has any other suggestion to maybe re-code all columns at once, but matching on Ref and (ideally) column name? Thank you AM

ammarhm · ‎03-16-2022

Of course! Genius! Thanks

ammarhm · ‎03-16-2022

Hi everyone, suppose we have two tables that w are combining to one: DATA table_1; input id censor gender $ ; DATALINES; 1 1 M 2 0 F 3 1 F ; run; DATA Table_2; input id treatment R1 R2 R3 response $ outcome $; DATALINES; 1 1 40 44 20 No B 1 1 45 46 16 Yes A 1 1 40 32 20 No B 1 1 47 32 23 No B 2 0 41 25 22 No B 2 1 54 35 13 No B 2 1 48 50 9 Yes A 2 1 36 33 12 Yes B 3 0 49 51 8 Yes A 3 1 49 52 10 Yes A 3 1 44 35 12 No A 3 1 49 50 8 Yes A ; run; proc sql; create table combined as select a.*, b.treatment, b.R1, b.R2, b.R3, b.response, b.outcome from table_1 a left join table_2 b on a.id=b.id ; quit; this will generate the following outcome: id censor gender treatment R1 R2 R3 response outcome 1 1 M 1 40 32 20 No B 1 1 M 1 47 32 23 No B 1 1 M 1 40 44 20 No B 1 1 M 1 45 46 16 Yes A 2 1 F 1 48 50 9 Yes A 2 1 F 1 54 35 13 No B 2 1 F 0 41 25 22 No B 2 1 F 1 36 33 12 Yes B 3 1 F 1 49 50 8 Yes A 3 1 F 1 44 35 12 No A 3 1 F 1 49 52 10 Yes A 3 1 F 0 49 51 8 Yes A This is of course great for data manipulation, but say i wanted to generate an output like this to put into an excel and give it to someone: id censor gender treatment R1 R2 R3 response outcome 1 1 M 1 40 32 20 No B 1 47 32 23 No B 1 40 44 20 No B 1 45 46 16 Yes A 2 1 F 1 48 50 9 Yes A 1 54 35 13 No B 0 41 25 22 No B 1 36 33 12 Yes B 3 1 F 1 49 50 8 Yes A 1 44 35 12 No A 1 49 52 10 Yes A 0 49 51 8 Yes A So basically data from table 1 appears only once and does not repeat itself. This is just to improve the visibility of the data, and the this table is not going to be used for analysis. Does anyone know how to generate this outcome? Thanks

ammarhm · ‎03-07-2022

HI everyone, I have a general and a SAS specific question. I am using SAS to create and maintain a large database that different sites will contribute information to. Crucial in this process that only de-identified information on patients will be stored in the database, and de-identification of data happens at the site before sending the data to the registry. There are two problems here, one easy, one much harder: 1. The easy problem: is there a built in procedure or a macro that anyone knows of, that can take a number of identifiers (patient's name, date of birth, Medicare ID, etc) and then generates a unique statistical ID that can be used in the database? 2. The difficult problem: The database should be able to identify repeat admissions/ data from the same patient at different sites. So the (de-identified) ID of a patient should be shared between the sites. However, as the process of de-identification should ideally happen at the site level before the data leaves to the registry, this poses a problem of how to be able to assign the same ID to the patient across multiple sites without the site sharing the identifiers between them. Has anyone dealt with a similar problem and how have you approached it? Thanks heaps.

ammarhm · ‎01-04-2022

Hi everyone, Could anyone suggest what to do to estimate median (or mean) survival time when less than 50% of observations fail? One example is to do the following: proc lifetest data=have nelson; time time*event(0); run; This will give the estimated mean (and standard error) survival time -Is there an easy way to get 95% CI (instead of manually calculating that from SE) - Is there another or preferred way of getting e3stimated median/ mean survival time? I read that you could use proc lifereg but not sure how to get survival time from that? Thanks

ammarhm · ‎01-03-2022

Thanks @Rick_SAS Here is the output you requested: SYSVLONG=9.04.01M6P110718 I think I have found the cause of the problem, I have made some changes to Stat.Lifetest.Graphics.SmoothedHazard using proc template. I went back and deleted this template to restore defaults and re-ran the code and the output looks fine now. This is the code I used initially to manipulate the template: proc template; delete Stat.Lifetest.Graphics.ProductLimitSurvival / store=sasuser.templat; delete Stat.Lifetest.Graphics.ProductLimitSurvival2 / store=sasuser.templat; delete Stat.Lifetest.Graphics.SmoothedHazard / store=sasuser.templat; delete Stat.Lifetest.Graphics.SmoothedHazard2 / store=sasuser.templat; run; proc template; source Stat.Lifetest.Graphics.SmoothedHazard; define statgraph Stat.Lifetest.Graphics.SmoothedHazard; dynamic NStrata Kernel Bandwidth confLimits GroupName maxTime _byline_ _bytitle_ _byfootnote_; BeginGraph; entrytitle ""; layout overlay / yaxisopts=(label="Estimated Hazard Rate" shortlabel="Hazard") xaxisopts=(linearopts=(viewmax=MAXTIME)); bandplot LimitUpper=UPPER LimitLower=LOWER x=TIME / displayTail=false modelname="Hazard" name="Confidence" LegendLabel= CONFLIMITS; seriesplot y=HAZARD x=TIME / name="Hazard"; endlayout; EndGraph; end; run; I did not deal with NSRATA >1 and group=STRATUM correctly in the template, I should have done it this way: BeginGraph; if (NSTRATA=1) entrytitle ""; layout overlay / yaxisopts=(label="Estimated Hazard Rate" shortlabel="Hazard") xaxisopts=(linearopts=(viewmax=MAXTIME)); bandplot LimitUpper=UPPER LimitLower=LOWER x=TIME / displayTail=false modelname="Hazard" name="Confidence" LegendLabel= CONFLIMITS; seriesplot y=HAZARD x=TIME / name="Hazard"; if (EXISTS(CONFLIMITS)) DISCRETELEGEND "Confidence" / location=outside halign=center valign=bottom; endif; endlayout; else entrytitle ""; entrytitle CONFLIMITS / textattrs=GRAPHVALUETEXT; layout overlay / yaxisopts=(label="Estimated Hazard Rate" shortlabel="Hazard") xaxisopts=(linearopts=(viewmax=MAXTIME)); bandplot LimitUpper=UPPER LimitLower=LOWER x=TIME / displayTail=false group=STRATUM index=STRATUMNUM modelname="Hazard" display=standard datatransparency=.5; if (EXISTS(MAXTIME)) seriesplot y=HAZARD x=TIME / name="Hazard" group=STRATUM index=STRATUMNUM curvelabel=BANDWIDTHLABEL curvelabellocation=inside curvelabelposition=start; else seriesplot y=HAZARD x=TIME / name="Hazard" group=STRATUM index=STRATUMNUM curvelabel=BANDWIDTHLABEL curvelabellocation=inside curvelabelposition=end; endif; discretelegend "Hazard" / title=GROUPNAME location=outside hAlign=center vAlign=bottom; endlayout; endif; if (_BYTITLE_) entrytitle _BYLINE_ / textattrs=GRAPHVALUETEXT; else if (_BYFOOTNOTE_) entryfootnote halign=left _BYLINE_; endif; endif; EndGraph; end; Now it is working correctly. Your output figure (the presence of the entry title "Epanechinkov Kernel-Smoothed Hazard Function") gave me the hint and reminded me of the cause Thanks again.

ammarhm · ‎01-03-2022

I have noticed a strange behaviour when requesting a stratified hazard plot from proc lifetest. To illustrate the problem, I have used the code and dataset from the following website: https://stats.oarc.ucla.edu/sas/seminars/sas-survival/ Specially, the code below should result in a stratified hazard plot as shown in the website: proc lifetest data=whas500 atrisk plots=hazard(bw=200) outs=outwhas500; strata bmi(15,18.5,25,30,40); time lenfol*fstat(0); run; However, the plot generated is a series of connected lines, and is not stratified and does not look like the on tin the website: I have tested this on multiple datasets with the same result, the lines are connected and not stratified. I tested this on SAS 9.4 and on SAS EG 8.1 with the same result! Any other analysis, such as a stratified Kaplan Meier works perfectly fine but for hazard plots the graphs just don't look right. Could anyone please test this and let me know if they get similar results and maybe suggest a solution? kind regards Am

ammarhm · ‎01-01-2022

Thank you Koen, The only issue is that this plots the cumulative hazard, which is not the same as the (instantaneous ) hazard rate that you get using proc lifetest with plots=hazard function. What I am trying to get is the adjusted hazard rate, and not the adjusted cumulative hazard plot. Much appreciated.

ammarhm · ‎01-01-2022

Hi everyone, Continuing the theme of hazard plots, you can get an (unadjusted) hazard plot using the example below: ods select HazardPlot; ods graphics on; proc lifetest data=sashelp.BMT plots=hazard; time T * Status(0); run; ods graphics off; Say that the variable (Group) is an important factor when it comes to the risk of developing the outcome of interest and hence you would want to adjust for it, you can of course do that with Cox regression: proc phreg data=sashelp.BMT ; class group; model T * Status(0)=group; run; So, going back to the plot of hazards, how can I get a hazards plot adjusting for the variable group? Thank you.

ammarhm · ‎12-31-2021

Solved using proc template and entrytitle keyword proc template; source Stat.Lifetest.Graphics.SmoothedHazard; define statgraph Stat.Lifetest.Graphics.SmoothedHazard; dynamic NStrata Kernel Bandwidth confLimits GroupName maxTime _byline_ _bytitle_ _byfootnote_; BeginGraph; entrytitle ""; layout overlay / yaxisopts=(label="Estimated Hazard Rate" shortlabel="Hazard") xaxisopts=(linearopts=(viewmax=MAXTIME)); bandplot LimitUpper=UPPER LimitLower=LOWER x=TIME / displayTail=false modelname="Hazard" name="Confidence" LegendLabel= CONFLIMITS; seriesplot y=HAZARD x=TIME / name="Hazard"; if (EXISTS(CONFLIMITS)) DISCRETELEGEND "Confidence" / location=outside halign=center valign=bottom; endif; endlayout; EndGraph; end; run;

ammarhm · ‎12-31-2021

The following code will produce a plot of the hazards function ods select HazardPlot; ods graphics on; proc lifetest data=sashelp.BMT plots=hazard; time T * Status(0); run; ods graphics off; I am trying to remove the title of the plot where it says "epanechnikov kernel-smoothed hazard function ", and to remove the "Bandwidth" comment below the graph I usually use the steps described in this documentation to customise my Kaplan Meier plots: https://support.sas.com/documentation/onlinedoc/stat/151/kaplan.pdf However, the above document does not address how to deal with hazard plots.. Any thought about how to remove the title or customise it in the hazard plot and how to remove the bandwidth comment? Thanks

ammarhm · ‎12-31-2021

Thanks @StatDave worked fine

ammarhm · ‎12-30-2021

Hi everyone, I am trying to calculate the standardised incidence ratio using event rates observed in a cohort of patients during our study and comparing that to expected rates in the background population of an outcome. The data file can be loaded using the code below, and contains age group (in 5 years intervals), follow up time in the study per age group expressed as person-year, the number of events observed in our study, and finally, the expected rates of the event of interest in the background population (these numbers are taken from the Australian Bureau of Statistics). data Study; infile cards dlm=" "; input AgeGroup PersonYear ObservedEvents ExpectedRate; datalines; 0 3.187542779 0 0.0000010000 5 17.83367557 0 0.0000010000 10 105.2190281 0 0.0000000000 15 228.3169062 0 0.0000010000 20 345.8637919 1 0.0000011667 25 347.238193 2 0.0000010000 30 325.2368241 0 0.0000015714 35 289.4476386 5 0.0000030323 40 217.0006845 2 0.0000056774 45 237.1403149 0 0.0000133548 50 268.3819302 3 0.0000255161 55 296.0999316 3 0.0000468065 60 228.3620808 2 0.0000705806 65 166.2388775 3 0.0001121935 70 90.23819302 1 0.0001589355 75 49.30732375 0 0.0002208065 80 23.21560575 2 0.0002692903 85 10.38877481 0 0.0003176452 ; run; Basically, you can use the follow-up time in person-year and multiply it by the expected event rate to get the number of expected events in the study population, and from there calculate the standardised incidence ratio using the approach described here: https://epiville.ccnmtl.columbia.edu/interactive/sir.html However, doing this manual calculation has a number of disadvantages, such as not (directly) getting the 95% confidence intervals and p-value . I have been doing some reading including experimenting with proc stdrate, but I cannot figure out how to use proc stdrate on my dataset. From what I understand you need follow up time and events in the background population (rather than the rates) to calculate SIR. I am quite stuck here, I cannot get the population number/ age distribution data for Australia to use in my calculation. I wonder if anyone could please assist in implementing stdrate or any other SAS procedure to calculate SIR from the table above? Thank you.

ammarhm · ‎09-29-2021

Thanks @ChrisNZ Very hepful indded. I only needed to read the documentation in more details, I found the rattrmap option seems to give control over how the colours are assigned. data myrattrmap; retain id "myID"; length min $ 5 max $ 5; input min $ max $ color $ altcolor $; datalines; _min_ 4 purple purple 4 6 gold gold 6 _max_ green green ; run; proc sgplot data=have rattrmap=myrattrmap; heatmapparm x=a y=i colorresponse=o / rattrid=myID outline discretex colormodel=ThreeColorRamp; text x=a y=i text=o / textattrs=(size=10pt) strip; by L ; gradlegend; run;

ammarhm · ‎09-29-2021

Hi everyone, I am using sgplot to create heat-maps to present data form a table. It is a great functionally, but I am struggling with the colour scale for the heat map. Basically, i am generating several heat maps, based on data from a table, the min and max for the data in each heat map are slightly different, resulting in the colour scale (autogenerated by SGPLIT) being different between the different heat maps, and hence the user cannot appreciate the difference easily. TO illustrate this, see the following code: * Generate a table with random numbers; Data have; do a= 1 to 3; do i=1 to 5; L=1; o=floor(rand('Uniform')*10); output; end; output; end; do a= 1 to 3; do i=1 to 5; L=2; o=floor(rand('Uniform')*5)+2; output; end; output; end; run; *Generate heat-maps; proc sgplot data=have; heatmapparm x=a y=i colorresponse=o / outline discretex colormodel=ThreeColorRamp; text x=a y=i text=o / textattrs=(size=10pt) strip; by L ; gradlegend; run; you will notice that the code above generates two heat maps but that cells containing the same number (say 6) in the two heat maps have different colour shades. Is there a way to (force) standardise the colour scale across the heat maps?

Online Status	Offline
Date Last Visited	‎03-06-2023 11:58 AM

SAS Support Communities

Re-coding/ matching multiple columns from an external table

Re: Combining tables and displaying first row from the first table whi...

Combining tables and displaying first row from the first table while k...

Dealing with (de-identified/ re-identifiable) data from multiple sites

Estimate mean or median survival time with 95% confidence interval.

Re: Strange behaviour of proc lifetest with strata

Strange behaviour of proc lifetest with strata

Re: Hazard plot adjusted for covariates

Hazard plot adjusted for covariates

Re: Customising Hazard pot title.

Re: Re-coding/ matching multiple columns from an external table

Re: Strange behaviour of proc lifetest with strata

Re: Sandarizing colour scale in heatmap

Re: Sandarizing colour scale in heatmap

Re: Cox regression vs Poisson regression for analysis

Re: Splitting follow up time by exposure status

Re: Strange behaviour of proc lifetest with strata

Re: Customising Hazard pot title.

Re: Sandarizing colour scale in heatmap

Re: Should I learn SAS or R?

Re-coding/ matching multiple columns from an external table

Re: Combining tables and displaying first row from the first table whi...

Combining tables and displaying first row from the first table while k...

Dealing with (de-identified/ re-identifiable) data from multiple sites

Estimate mean or median survival time with 95% confidence interval.

Re: Strange behaviour of proc lifetest with strata

Strange behaviour of proc lifetest with strata

Re: Hazard plot adjusted for covariates

Hazard plot adjusted for covariates

Re: Customising Hazard pot title.

Customising Hazard pot title.

Re: Calculating standardised incidence ratio from observed and expecte...

Calculating standardised incidence ratio from observed and expected ev...

Re: Sandarizing colour scale in heatmap

Sandarizing colour scale in heatmap

Follow Us

What is...