SAS Support Communities

Hello! I have a dataset that is in long format. Each subject is supposed to have an outcome score at timepoints: 0 hours, 5 minutes, 15 minutes, and 30 minutes.   My data look like this:   data datafile; input subject_number timepoint $ minutes score remove ; datalines; 1 0h 0 10 0 1 5m 5 9 0 1 10m 10 10 0 1 15m 15 10 0 1 30m 30 11 0 2 0h 0 11 0 2 5m 5 . 0 2 15m 15 10 0 2 30m 30 . 1 ; run; These data only consist of two subjects for simplicity sake.    Variables: subject_number = subject numer timepoint = timepoint each response variable was taken at minutes = x-axis variable. This is the numeric version of timepoint  score = the y-axis/dependent variable remove = this variable = 1 if either endpoint is missing (0h or 30 min)   My code would look something like this [with some of my own notes inserted as comments]: /*Note: If subject is missing start time (0h) or end time (30m), then remove = 1*/ /*use the 'linear up, log down' method'*/ data datafile2; set datafile; /* linear formula: auc = 1/2* (score_i +score_i+1) * (t_i+1 - t_i)*/ /*When I had calculated area using only the linear trapezoidal method, this is what code I used: */ lagtime = lag(minutes); lagvalue = lag(score); if minutes = 0 then do; lagtime = 0; lagvalue = 0; end; trapezoidScore = (minutes-lagtime)*(score + Lagvalue)/2; SumTrapezoidScroe + TrapezoidScore;*/ /*log forumla: (score_i - score_i+1)/(ln(score_i)-ln(score_i+1))*(t_i+1-t_i)*/ run;   Basically, what I need to code is: (a) If the score at timepoint i+1 is greater than or equal to the score at timepoint i, then use the linear trapezoidal method to calculate the area from timepoint i to timepoint i+1. (b) If the score at timepoint i+1 is less than the score at timepoint i, then use the logarithmic trapezoidal method to calculate the area from timepoint i to timepoint i+1.   The linear trapezoidal method formula (what you'd use to calculate the area going 'up') is:      where C1 and C2 are the y values (scores in our case), and t1 and t2 are the timepoints on the x-axis.   The logarithmic trapezoidal method formula (what you'd use to calculate the area going 'down' is:    I took these formulas and this idea of "linear-up log-down" from this short article . If, say, the score at timepoint i+1 is missing, but is not missing at timepoint i and timepoint i+2, then calculate the area but using timepoint i and timepoint i+2's scores and timepoints (and depending on if the score at timepoint i >= or < the score at timepoint i+2, use either the linear trapezoidal method or logarithmic trapezoidal method. I've indicated such timepoints as you can see for subject 2 at 5 minutes.    Examples in the sample data:  Subject 1 from 0h to 5m, his score goes down from 10 to 9. Here, we'd use the logarithmic trapezoidal method formula to calculate the area under the curve for this section: AUC_0h_5m =  [(10 - 9)/(ln(10) - ln(9))]*(5-0) Subject 1 from 5m to 10m, his score goes up from 9 to 10. Here, we'd use the linear trapezoidal method formula to calculate the area under the curve for this section: AUC_5m_10m = 0.5(9 + 10) * (10 - 5) Subject 1 from 10m to 15m, his score remains the same. Use the linear trapezoidal method formula to calculate the area under the curve for this section: AUC_10m_15m = 0.5(10+10) * (15-10) Subject 2 has a score at 0h and 10m, but is missing score at 5m. Also, his score went down from timepoint 0h to 10m. Use the logarithmic trapezoidal method to calculate the area under the curve for this section: AUC_0h_15m = [(11-10)/(ln(11)-ln(10))]*(15-0) Subject 2 is missing a score at the last timepoint, which is 30m. We will ignore this part. Then, take all of the AUC's calculated above (and the timepoints that I didn't give an example for) and sum them per subject.   I need help on how to do this since my data are in long format. How do I tell SAS to skip to the next record, and look back at the previous record, depending on which is bigger, implement a different formula, and if the timepoint doesn't exist, then skip and go to the next one? Here, some thing to note: I don't always have a record such as subject 2, 30m, where the score is ".".. sometimes they just don't have a record at all, and sometimes it's there as "." (missing). For sake of this example, I just didn't include a 10m record for subject 2. So SAS would need to know to skip over the missing 10m..    Thank you so much!!! Best, Gina ... View more

Hi there,   I have a dataset with long text fields. I need to replace every instance of a dollar amount, say, "439.33" with "439.33USD"   Here's a sample data set:     data one; infile cards ; length name $200; input name; cards; Ccy="USD">738.56</NS!figlwijfoaiu345Ccy="USD">38.44</ScT@ run; So what I have is:     What I want is USD to be present after every dollar amount as is here:   I found two different community forums, and know that I will need a combination of index To find the decimal point that follows any "USD" in the text substr tranwrd functions To replace that dollar amount with [dollaramount]USD There could be multiple instances of Ccy="USD">[dollar amount] in the string. I just want to tack on the USD after the dollar amount, but not every dollar amount is XYZ.AB, some are YZ.AB, some are Z.AB etc. There will always be a decimal point with two decimal places though that follow the Ccy="USD"   Thanks!     ... View more

**Note: Modified and edited today 8/17/2018:**   Hi,   I am using SAS 9.4. I have a dataset where each ID can be repeated. So there are multiple records per ID. Each record has a start and an end date. I am trying to create a categorical variable based off those whose enrollment ended sometime in 2016 whose enrollment began sometime in 2016 that were enrolled at the start and end of 2016 but had 2016 enrollment gaps that were enrolled in the entire calendar year 2016 You know if someone's enrolled if their status is "active."   Here's a sample data set: data have; input ID status (start end) (:mmddyy10.); format start end yymmdd10.; cards; 1A active 01012011 02012011 1A revoked 02012011 05012013 1A active 05012013 06032016 1B active 05032016 07012016 1B revoked 07012016 . 2A active 03012016 05012018 2A deactivated 05012018 06012018 2A active 06012018 . 3A active 12092003 09062016 3A deactivated 09062016 09272016 3A active 09272016 . 4A active 01032015 02012015 4A deactivated 02012015 05042015 4A active 05042015 03032017 4A deactivated 03032017 . 4B active 03032015 . ; data have2; set have; format start mmddyy10.; format end mmddyy10.; run; As you can see, the ID corresponds with the categorical variable in my sample data set.   So if someone is revoked or deactivated, that means they are not active. Now: ID 1A and 1B are category 1, "Suppliers whose enrollment ended sometime in 2016" (1A's last active end date is 06/03/2016, and 1B's last active end date is 07/01/2016. 2A falls under "Suppliers whose enrollment began sometime in 2016" 03/01/2016, 3A falls under "Suppliers that were enrolled at the start and end of 2016 but had 2016 enrollment gaps" as you can see on that they became deactivated from 9/6/2016-9/27/2016, and then active again on 9/27/2016. 4A and 4B fall under "enrolled the entire calendar year of 2016" I should mention that a missing in the end date means that they're ongoing. So for 4b, they were active on 3//2015, and have been active since then, since there is a "." for the last end date.     I  have this sql code: proc sql; create table want as select id,  min(start) as start, max(end) as end from have group by id; quit;   but the problem is, this doesn't help me find category 3. Even so, I'm not sure how to completely categorize them into 4 from this.   Then I tried this after sorting by id start end:   data want; set have; retain startA endA  gapflag started2016 ended2016 startedbefore16; format startA mmddyy10.; format endA mmddyy10.; by id start end; if first.idthen do; startA= start; endA= end; if start  >= 20454  and start <= 20819 then started2016 =1; if end >= 20454  and end <= 20819 then ended2016 = 1;  if start < 20454 then startedbefore16 = 1; end;   else do; if endA >= 20454 and end <= 20819 and start- endA > 1 then gapflag = 1;  end; run;   But this didn't quite work either.   So I tried this:  data want; retain start; format start end laststartdate mmddyy10.; set have; by ID; if first.ID then startA = start; if last.ID then do; endA = end; laststartdate = start; output ; end; run; data want2; format cat2016 cat16f.; set want; if startA <'01JAN2016'd and endA >= '01JAN2016'd and endA <= '31DEC2016'd then cat2016 = 1; else if (startA >= '01JAN2016'd and startA =< '31DEC2016'd then cat2016 = 2; else if (startA < '01JAN2016'd and laststartdate > '31DEC2016'd) or (laststartdate >= startA and startA < '01JAN2016'd and end = .) then cat2016 = 4; /*CREATE CONTINUOUS VARIABLE -N UMBER OF DAYS ACTIVE IN 2016*/ /*assume active if end = .*/ if endA = . and startA < '01JAN2016'd then daysin2016 = 365; /*for those who ended sometime in 2016; start can be any time*/ if endA >='01JAN2016'd and endA <'31DEC2016'd then daysin2016 = intck('day', '01JAN2016'd, endA); /*For those who start and end in 2016*/ if startA >='01JAN2016'd and startA < '31DEC2016'd and endA >='01JAN2016'd and endA <'31DEC2016'd then daysin2016 = intck('day', startA, endA); if startA >= '01JAN2016'd and startA < '31DEC2016'd then daysin2016 = intck('day', startA, '31DEC2016'd ); run;   However, this was wrong as it didn't take into account what it means to actually not be active in 2016 (I didn't take the status variable "active, revoked, deactivated" into account). My goal is to get a dataset that looks like this:   data finalwant; input ID status (start end) (:mmddyy10.) CAT2016 daysin2016; format start end yymmdd10.; cards; 1A active 01012011 02012011 1 154 1A revoked 02012011 05012013 1 154 1A active 05012013 06032016 1 154 1B active 05032016 07012016 1 59 1B revoked 07012016 . 1 59 2A active 03012016 05012018 2 305 2A deactivated 05012018 06012018 2 305 2A active 06012018 . 2 305 3A active 12092003 09062016 3 344 3A deactivated 09062016 09272016 3 344 3A active 09272016 . 3 344 4A active 01032015 02012015 4 365 4A deactivated 02012015 05042015 4 365 4A active 05042015 03032017 4 365 4A deactivated 03032017 . 4 365 4B active 03032015 . 4 365 ; Thanks!!! Best, Gina ... View more

Hi all,   I apologize if this has been answered elsewhere. I tried to search for it on the boards but couldn't find the exact answer.   I have a list of, say, 10 variables. I want to run a macro on the first 3 variables and the 7th, 8th and 9th variables. How would I go about indicating this? Let't just keep it to something simple like Proc Freq only for the sake of this example (but I end up manipulating the output and doing other things with it, and then doing other things with the other variables). The problem is that I usually know to say something like "%do i = 6 %to 9'" but in this case, here I want to say "%do i = 1 %to 3, and i = 7 %to 9;"   The reason I'm doing this  is because I'll run the same commands on those sets of variables, and manipulate them the same way. And then I'll manipulate the output of the other frequencies a different way. The only other alternative I can think of is grouping said variables together in a list and running a loop on that group only, a different loop on a different group, etc.       %let list = q1 q2 q3 q4 q5 q6 q7 q8 q9 q10; %macro q9freq; %do IncMiss = 1 %to 2 /*We want to run this all including missing, and not including missing*/ /***********************please check here***********************************/ %do 1 %to 3, 7 %to 9; /*This doesn't work, but I don't know how to fix it*/ /****************************************************************************/ %let var = %scan(&list, %eval(&i)); proc freq data = irblabel_1; tables &var/ list %if &IncMiss = 1 %then missing ; /*this semi colon closes the %if statement*/ ; /*This semicolon closes the tables statement*/ ods output Freq.Table1.OneWayFreqs = Freq&var.A&IncMiss; run; data freq&var.b&IncMiss (keep = Vname Answer PercentN); length Vname $256; format Vname $256.; retain Vname F_&var PercentN; set freq&var.A&IncMiss; where index(F_&var, "Yes") > 0 or index(F_&var, "Not") > 0 or index(F_&var, "Missing") > 0; Vname = scan(table, 2, " "); PercentN = round(percent,0.1); Answer = f_&var;/* Get the first word, blank delimited */ run; data Q9100&IncMiss; retain vname label length label $256; length vname $256; length answer $256; format label $256.; format vname $256.; format answer $256.; set blankq9 freqq1b&IncMiss freqq2b&IncMiss freqq3b&IncMiss freqq7b&IncMiss freqq8b&IncMiss freqq9b&IncMiss ; %MACRO LABELQ9(OLD, NEW); if Vname = &OLD then label = &new; %mend; %LABELQ9(" ", "label1:"); %LABELQ9("q1", "label2"); %LABELQ9("q2", "blah blah?"); %end; /*End loop going thru Q9-12*/ %end; /*End loop going thru missing and nonmissing*/ %mend; %q9freq;       ... View more

Hi!   I have a tricky problem with this data set. It’s longitudinal (we are comparing 2017 vs. 2015), and there are survey weights. However, most of the outcomes of interest are dichotomous. The PhD statistician on our team suggested that, even though they’re dichotomous, I could still compute the difference between each variable, say “did you use this book in 2017” vs. “did you use this book in 2015”? and the possibilities for the difference are: 0, 1, -1.  He said it’s fine if I just test that this difference = 0 (even though technically it’s not a continuous variable). I can't regress used_book1_in_2017  = used_book1_in_2015 bc we'd need to incorporate a fixed effects model in this. So we're keeping it simple for now.   What I’ve been seeing online is that folks suggest that you just test to see if that difference = 0 in surveymeans. I’m not sure how to do that though? I know you can use the domain statement, but the problem is, I just want to test if my difference variable = 0. There is no group indicator. Here, difft1 = used_book1_in_2017 – used_book1_in_2015. difft1 can = 0,1, -1.   This is what I have so far: proc surveymeans data = atp1517b;      weight weight;       var difft1; run;   But this just gives me the mean, SE and CI. Any advice? Thanks!   ... View more

I want to run the following code (see below). I am looping through all the y variables within my macro and calling the x variables when I call the macro "simpleC" in. My question is this: How can I simultaneously loop through two variables within the loop? I know how to do this with an array in a datastep but not within a macro.   So when y = outcomevar1, we need to have   domain subset_cat*subsetvar1   and when y= outcomevar2, we need to have   domain subset_cat*subsetvar2.   Is there a way I can modify the     %do i = 1 %to %sysfunc(countw(&varlist)); /*Modify this!!!*/ %let tabit = %scan(&varlist, %eval(&i)); %let subset = %scan(&varlistB, %eval(&i)); /*This is what I thought I could do, but is incorrect*/                     lines to include this? It doesn't like the  %let subset = %scan(&varlistB, %eval(&i));     line. I'm already looping through the y variables but I don't know how to match two variables and loop through a set at the same time. I don't want to have a nested do loop because I don't want to run, say y = outcomevar1 and subsetcatvar2 at the same time.  I tried searching for this in the previous answered questions on the boards, but was unable to figure it out. I really do appreciate your help and hope that my code and explanation is sufficient. If not, I can further elaborate. Thanks! Best, Gina   Here is my code: %let cont = contvar1 contvar2; %let MHcont = outcomevar1               outcomevar2;   %let varlistB = subsetvar1                 Subsetvar2;   %macro simpleC(xvar, output, model);       %do o = 0 %to 1;             %if &o = 1 %then %do;                   %let outType = MH;                   %let dom = Domain4. ;                   %let varlist = &MHcont ;             %end; /*for MH variables*/             %else %do;                   %let outType = ;                   %let dom = ;                   %let varlist = &cont;             %end;       /*Now, we do this for each outcome*/                   %do i = 1 %to %sysfunc(countw(&varlist)); /*Modify this!!!*/                   %let tabit = %scan(&varlist, %eval(&i));                   %let subset = %scan(&varlistB, %eval(&i)); /*This is what I thought I could do, but is incorrect*/   proc surveyreg data = &cleanData;       class &xvar;       model &tabit = &xvar  / solution ;       weight ps_wgt;       strata strata_cat;       %if &outType = MH %then domain subset_cat * &subset;;       ods output       %if &outType = MH %then SurveyReg.DependentVariable.&dom.DomainSummary       = aDataSummary;       %else                SurveyReg.DependentVariable.&dom.DataSummary       = aDataSummary;                                     run; %end; /*This ends the loop around each outcome (i=1 to n.outcomes) and its corresponding subsetting variable (see &varlistb)*/ %end; /*This ends the loop looking at MH vs non-MH outcomes. (o = 0 to 1)*/   %simpleC (race7,        /*Variable used*/           race,         /* name of dataset to be written: outcome_race      */           1             /* Model number - used for stacking bivariates then merging w/ other model results later */ );   %simpleC (%str(race7               q08               q127),                          All,   /* name of dataset to be written: outcome_All                                                         */           5       /* Model number - used for stacking bivariates then merging w/ other model results */ );       ... View more

  Hello,   I want to bold certain rows that I'm printing out using proc report and ods EXCELXP tagset, and then indent the rows that don't get bolded. How can I do this? It's not showing up in my output 😞  The indented rows show up as indented in the dataset (see attached picture). When I use proc report they also look indented (see attached picture). However, when I use ods tagsets to get the report to excel, the rows are not indented (see attached picture).   Here is some sample code: PROC REPORT DATA = tempdata; column classval0 ORCL_M1 p_M1 ORCL_M2 p_M2 ORCL_M3 p_M3 ORCL_M4 p_M4 ORCL_M5 p_M5; /*align the p-values by decimal places, also highlight the significant ones*/ define ORCL_M1 / display center style(column) = [background=sigf.]; define p_M1 / display center style(column) = [background=sigf. just = d]; define classval0 / DISPLAY style=[asis=on]; /*We want to bold the following words in classval0*/ compute classval0; if classval0 in ("Intercept", "Race (REF = White)" , "Female" , "LGBT (REF = Heterosexual" , "Marital status (REF = Married)" , "At least 1 child <18" , "Never deployed (REF = Ever deployed)" , "Combat zone deployments (REF = 3+ deployments)" , "Length of deployment (REF = No deployment in past 12 mo)" , "Service Branch (REF = USCG)" , "Pay Grade (REF = O1-O10)" , "Age Group (REF = 25-34)") then call define ('_c1_', "style", "style = [font_weight=bold]"); /*I want to indent the words that we did not bold in classval0*/ else call define ('_c1_', "style", "style = [rightIndent]"); endcomp; run;      I found some useful sugi reports that other users posted in similar questions to this (that's how I thought to use the "rightindent" statement, even though I dont' think I used ti correctly), as well as the justify=d and font_weight=bold option. Something is off about mine though 😞   in my macro to create an excel sheet, i use the styles.xlstatistical option.     Any advice and help would be greatly appreciated. Thank you!   PS - I've tried to post this question 3 times and it got rejected so I'm hoping this is useful. ... View more