Your asking on the wrong forum.   But seriously the issue is how EXCEL is interpreting the file.  If you really need control how EXCEL is going to read your data then you should make an XLSX file instead of a CSV file.  CSV files do not have any place to store information about variable types or display formats. A CSV file is just a text file.     Both of your methods should produce the same file.  Try a simple example and actual look at the CSV file (don't let EXCEL open it and change it first just look at at with any text editor).   Try this test: filename csv 'myfile.csv'; data test; do date=today() to today()+3; datestring=put(date,date9.); output; end; format date date9.; run; proc export data=test dbms=csv file=csv ; run; data _null_; infile csv ; input; list; run; So running that code today I created a CSV file that looks like this: date,datestring 22FEB2024,22FEB2024 23FEB2024,23FEB2024 24FEB2024,24FEB2024 25FEB2024,25FEB2024 So there is no difference between the actual date value formatted to print with DATE9. format and the character variable with dates already formatted in the style that the DATE9. format produces.  So it makes sense the Excel will treat both columns the same since in the CSV file they ARE the same.   ... View more

What you do depends on what exactly is the research question.  Nonetheless, to make this a typical logistic regression problem you need a yes/no outcome. You could form that by having the clinicians select a meaningful threshold, like TV reduced by 10%, yes/no.  That makes for a logistic regression, but dichotomizing data often reduces power, and sort of muddies the interpretation.  In my example, a reduction of 9.9% is a 'no' while 10.00 is a 'yes', but is there a meaningful difference in these two volumes?   Relative % change = (TV1-TV3)/TV1 = 1-TV3/TV1.  Even if TV is normally distributed the ratio TV3/TV1 almost surely isn't normal.  From your data example it doesn't look like TV2 is used.   So you could use the Kruskal-Wallis test, available with the Wilcoxon option in proc npar1way, to compare groups and not have to be concerned with parametric assumptions.   If the overall test is significant, the DSCF for the pairwise comparisons.   There is a literature, mostly based on simulations, suggesting that ANCOVA with TV1 as the covariate is the preferred analysis.  Here is an easy read on the subject:  https://www.ncbi.nlm.nih.gov/pmc/articles/PMC34605/       ... View more

I found this interesting! ... View more

@Reeza  The graphing. I submitted the graphing to my mentor and he is requesting the I use excel because he wanted an improved labelling of the title axis, vertical and horizontal title axis. I looked at the macro for plotting the graph and its very confusing to me- am unable to manipulate the code to get the expected labels. So I wanted to know if I export my data into excel, can I plot the graph using excel? ... View more

The section of the note that I referred to shows an example of obtaining and interpreting the results. ... View more

Both codes can accomodate this change:   data wantPG; do i = 1 by 1 until (last.name); set readin; by name notsorted; end; length v $16; select (i); when (1) v = "1 visit"; when (2) v = "2 visits"; otherwise v = "3 visits or more"; end; drop i visit; rename v = visit; run; proc format; value visits 1 = "1 visit" 2 = "2 visits" other = "3 or more visits" ; run; proc sql; create table wantKB as select max(id) as maxId, name, max(visit) as lastVisit format=mmddyy10., count(*) as visit format=visits. from readin group by name ; quit; ... View more

@CathyVI wrote: @ballardwThank you. Your response answered my first question. To answer your question about limited space... YES, I have limited space for such enormous data. Any suggestion for question 2? 2. How can I find the overall freq and percentage of each variable across all the years combined (2006-2020) without joining the files. 2: Create data sets with the summary counts.    combine those data sets    use the combined data with proc freq using the Existing Freq variable as a FREQ variable in proc freq. However the "easy" data set to create from Proc Freq for multiple variables is using the ODS OUTPUT ONEWAYFREQ table that has a different structure. Make the sets with something like. The below code is using the option OBS= 10 to only use 10 observations from each set so that you can 1) test the code in relatively short time and 2) look at the output data sets created. %macro simplefreq (startyear=2006 , endyear=2022 ); %do i= &startYear. %to &endyear. ; Title "Summary Counts for Year &i."; ods output onewayfreqs= freqs&i. ; proc freq data=yourset&i (obs=10).; run; %end; %mend; %simplefreq; Really, look at the output sets and then ask about how to the next step. Caveat: if you have any formats that group values this output uses the FORMATTED values to count. If you do not want that behavior you need to clear the formats for the duration of the Proc freq. But that may mean you don't recognize values, especially with dates, times and datetimes. ... View more

You would have to generate separate TABLES statements and separate output dataset names. If you want the DIAG1 to DIAG12 to end up in the same variable in the output then use the RENAME= dataset option when making the datasets. %macro around(start_diag=,end_diag=); proc freq data=icd10_06_15 ; %do diag=&start_diag %to &end_diag; table diag&diag / out=ao&diag(rename=(diag&diag=diag)) noprint; %end; run; data ao_list ; set ao&start_diag - so&end_diag ; by diag; run; proc summary data=ao_list ; by diag ; output out=ao sum(count)=count; run; proc sort data=ao; by descending count; run; %mend; Note that if the same subject has the same diagnosis listed under more than one of the DIAGx columns then that subject will be counted more than once. To get unique subject counts first transpose the data and then eliminate the duplicates before counting.  You will need a set of BY variables that uniquely identifies the observations in the original dataset.  Let's assume that variable is ID. proc transpose data=icd10_06_15 out=tall(rename=(col1=diag); by id; var diag1-diag12 ; run; proc sort data=tall nodupkey; by id diag; run; proc freq data=tall order=freq ; tables diag / noprint out=ao ; run; ... View more

There seem to be some holes in your rules. First let's try it with only 2 input variables.  First let's make a dataset with all possible combinations of 0,1 and 2. data have; do drug1=0,1,2; do drug2=0,1,2; output; end;end; run; Now let's apply your rules (in the order you state them). %let list=drug1 drug2 ; data want; set have; /* 1. if any of the drugs = 0 then drug = 0 */ if countc(cats(of &list),'0') then drug=0; /* 2. if only one drug=1 then drug =1 */ else if 1=countc(cats(of &list),'1') then drug=1; /* 3. if More than one drug= 2 then drug= 2 */ else if 1<countc(cats(of &list),'2') then drug=2; /* 4. ???? */ else drug=.; run; Result So none of your rules covers the case when there are no zeros and more than 1 one and less than 2 twos.     ... View more

It is part of the PROC statement itself. Try any of these locations. data _null_; do year=2006 to 2019; call execute(catx(' ' ,'proc sort nodupkey data=', cats('claims_rx_',year) ,';', 'by pat_id;','run;' )); end; run; Or data _null_; do year=2006 to 2019; call execute(catx(' ' ,'proc sort data=', cats('claims_rx_',year) ,'nodupkey;', 'by pat_id;','run;' )); end; run; or data _null_; do year=2006 to 2019; call execute(catx(' ' ,'proc sort data=', cats('claims_rx_',year) ,'nodupkey',';', 'by pat_id;','run;' )); end; run; ... View more

Concatenate all yearly datasets into one (use a data step view, and derive the year with INDSNAME=), merge with your unique dataset, and select the demographics data in a DO UNTIL loop. For code, please supply usable example data in data steps with DATALINES. ... View more

Why do you read a number as character? ... View more

@CathyVI wrote: @PaigeMiller @Cynthia_sas @ballardw  Thank you so much for the codes and the resources. The code works perfectly. Do you think macro will work for a merge file? Most importantly since I have to proc sort the two dataset before merging. I have a code example below proc sort data=out_2006; by id; run; proc sort data=out_2007; by id; run; Data out_06_07; merge out_2006 (in=a) out_2007 (in=b); by pt_id clm b m mb ; if b or a; run;   Hello @CathyVI remember the advice I stated in my first post in this thread? I said you need to create working SAS code without macros. I don't think (and others don't think) this is working code, in the sense that it does not do what you want. Could you make sure that it works and accomplishes the desired task by looking at the output data set named OUT_06_07 to see if it contains the desired information. ... View more

You really must show us what you expect as a result from this example datasets.   And do you really store your dates like this, and a month as character? Or should your data be this: data data1; infile datalines truncover; input ID$ ID2$ DT1 :mmddyy8. X Y Z DT2 :yymmdd8.; format dt1 dt2 yymmddn8.; datalines; l2020642 F223220 20190620 0 0 1 20190620 l2020642 Z799199 20180607 1 0 0 l2020642 Z837743 20180418 0 0 1 l2020642 Z242142 20181221 0 0 1 ; For your second dataset, I would recommend to combine your month/year into a SAS date: data data2; input ID$ MTH YR; monyear = mdy(mth,1,yr); format monyear yymmn6.; datalines; l2020642 08 2017 l2020642 01 2010 ; This enables you to use SAS tools for dates (INTCK, INTHX and so on). ... View more

@PaigeMiller Thank you. I will always remember to put the code in the insert code.  ... View more