Yes, I also use a different data step in addition to the first one. see below.  Data CO_Hosp(where=(COpoisoning=1)); Set 'C:\Users\hjt09632\Documents\SAS Dataset 2022\complete22.sas7bdat'; array DXS[18] $ dx1-dx18; COyes_step2a10 = 0; COno_step2b10 = 0; CO_fire10 = 0; CO_nonfire_prelim10 = 0; CO_unknown_prelim1_10 = 0; do z= 1 to 18; * sub-step A; if substr(DXS {z},1,5) IN:('T5801', 'T5804', 'T5811', 'T5814', 'T5891', 'T5894') or substr(DXS {z},1,6) IN: ('T582X1', 'T582X4', 'T588X1', 'T588X4') then COyes_step2a10 = 1 ; * sub-step B; if substr(DXS {z}, 1,5)IN: ('T5802', 'T5803', 'T5812', 'T5813', 'T5892', 'T5893') or substr(DXS {z},1,6) IN: ('T582X2', 'T582X3', 'T588X2', 'T588X3') then COno_step2b10 = 1; if COyes_step2a10 = 1 or 0 and COno_step2b10 NE 1 then COpoisoning = 1 ; *differentiate causes: sub-steps C, D, E, F; * sub-step c; if substr(DXS {z}, 1,3)IN:('X00', 'X01', 'X02', 'X03', 'X04', 'X05', 'X06', 'X08') then CO_fire10 = 1; * sub-step d; if substr(DXS {z},1,5) in ('T5801', 'T5811', 'T5891') or substr(DXS {z},1,6) in ('T582X1', 'T588X1') then CO_nonfire_prelim10 = 1; if CO_fire10 NE 1 and CO_nonfire_prelim10 = 1 then CO_nonfire10 = 1; * sub-step e; if substr(DXS {z},1,5) IN: ('T5804', 'T5814', 'T5894') or substr(DXS {z},1,6) IN: ('T582X4', 'T588X4') then CO_unknown_prelim1_10 = 1; END; if CO_fire10 ~= 1 and CO_unknown_prelim1_10 = 1 then CO_unknown_prelim2_10 = 1; if CO_fire10 = 1 and CO_nonfire10 = 1 then CO_unknown_prelim3_10 = 1; if CO_unknown_prelim2_10 = 1 or CO_unknown_prelim3_10 = 1 then CO_unknown10 = 1; * sub-step f; if CO_fire10 = 1 then Cause = 1;*fire related ; if CO_nonfire10 = 1 then Cause = 2;*not fire related; if CO_unknown10 = 1 then Cause = 3;*Unknown mechanism or intent; if CO_fire10 = 1 and CO_nonfire10 = 1 then Cause = 3; if CO_fire10 = 1 and CO_unknown10 = 1 then Cause = 3; if CO_nonfire10 = 1 and CO_unknown10 = 1 then Cause = 3; HealthOutcomeID = 3; /*Step 6: Create according to data dictionary*/ MonthlyHosp = 0; /*Step 7: Create a variable called “MonthlyHosp” and set it to zero for all rows.*/ run; proc sql ; create table rr as select distinct AdmissionMonth ,AgeGroup , County , Ethnicity , Race , HealthOutcomeID , MonthlyHosp , Sex , YearAdmitted , sum((Cause=1)) as IncidentCountFire , sum((Cause=2)) as IncidentCountNonFire , sum((Cause=3)) as IncidentCountUnknown from count_CO group by AdmissionMonth , AgeGroup , County , Ethnicity , Race , HealthOutcomeID , MonthlyHosp , Sex , YearAdmitted ; quit; data CO_Finalhosp; RETAIN RowIdentifier AdmissionMonth AgeGroup County Ethnicity HealthOutcomeID MonthlyHosp Race Sex YearAdmitted; set rr; RowIdentifier=_n_; KEEP RowIdentifier AdmissionMonth AgeGroup County Ethnicity HealthOutcomeID MonthlyHosp Race Sex YearAdmitted IncidentCountFire IncidentCountNonFire IncidentCountUnknown ; run;   ... View more

A quick test with a data set of 100,000,000 observations and testing time between @s_lassen's sort check and Proc sort execution: 2194 data work.sorttest; 2195 do id= 1 to 1E7; 2196 do order= 1 to 10; 2197 value= rand('uniform'); 2198 output; 2199 end; 2200 end; 2201 run; NOTE: The data set WORK.SORTTEST has 100000000 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 11.65 seconds user cpu time 4.42 seconds system cpu time 1.35 seconds memory 391.90k OS Memory 42016.00k Timestamp 09/11/2024 02:27:34 PM Step Count 122 Switch Count 0 2202 options fullstimer; 2203 %let sorted=NO; 2204 data _null_; 2205 do until(eof); 2206 set work.sorttest (keep=id order) end=eof; 2207 by id order; 2208 end; 2209 call symputx('SORTED','YES'); 2210 run; NOTE: There were 100000000 observations read from the data set WORK.SORTTEST. NOTE: DATA statement used (Total process time): real time 12.31 seconds user cpu time 10.96 seconds system cpu time 0.68 seconds memory 553.53k OS Memory 42016.00k Timestamp 09/11/2024 02:27:47 PM Step Count 123 Switch Count 0 2211 2212 %put &=sorted; SORTED=YES 2213 2214 proc sort data=work.sorttest; 2215 by id order; 2216 run; NOTE: There were 100000000 observations read from the data set WORK.SORTTEST. NOTE: The data set WORK.SORTTEST has 100000000 observations and 3 variables. NOTE: PROCEDURE SORT used (Total process time): real time 41.89 seconds user cpu time 29.75 seconds system cpu time 5.11 seconds memory 1064875.09k OS Memory 1104732.00k Timestamp 09/11/2024 02:28:29 PM Step Count 124 Switch Count 0 Proc sort would likely run slower with lots of variables as more data swapping goes on, BUT in that case TAGSORT would likely reduce the time to something similar to shown. YMMV if 1) the data is actually in an external database 2) sorting data over a network connection as multiple load and bandwidth factors come into play   The time difference I see here is likely less than getting a cup of coffee in a near-by break room. With data in this order of magnitude I might say just use proc sort and get a cup of coffee (or tea or ...).   So how "huge" is "huge"? As in number of observations and variables?   ... View more

@Sarath_A_SAS  You're replying to a very old discussion here.... In this method, you use the  $upcase.  format to ensure that all character variables in your dataset are stored in uppercase format. Actually: Using format $upcase. will DISPLAY/PRINT the characters uppercase but it will not change how they are stored. If you want to change how they are stored you need to rewrite the values using code like: data all_upper; set sashelp.class; array cvars {*} _character_; do _i=1 to dim(cvars); cvars[_i]=upcase(cvars[_i]); end; drop _i; run; ... View more

data duplicates non_duplicates; set test; by date value; /* If the current observation is NOT both the first and last occurrence, it is a duplicate */ if not (first.value and last.value) then output duplicates; else output non_duplicates; run; To achieve this task, you can create two datasets in SAS: one that contains duplicates based on date and value (including single records for a given date) and another dataset that contains non-duplicate records by date and value . Here's how you can do this using PROC SQL and DATA STEP approaches. Step 1: Prepare the Sample Data data test; input date :$10. id value $; datalines; 22Mar 1 x 22Mar 2 x 22Mar 3 x 24Mar 1 x 24Mar 2 x 28Mar 1 x 30Sep 1 x 15Jan 1 x 15Jan 2 y 15Jan 3 x ; run; proc sql; create table duplicates as select * from test group by date, value having count(*) >= 1; /* this includes both duplicates and single records */ quit; data duplicates non_duplicates; set test; by date value; /* If the current observation is NOT both the first and last occurrence, it is a duplicate */ if not (first.value and last.value) then output duplicates; else output non_duplicates; run;   ... View more

  /* Importing the Reference Table */ data reference_table; input ID_key Minutes Hospital $; datalines; 1 4 A 2 1 B 3 8 A 4 6 C 5 9 F ; run; /* Importing the Reference Table */ data table2; input ID_key score$; datalines; ID key Score 1 1 1 3 2 2 3 4 1 4 5 5 ; run; data table3; input ID_key score2$; datalines; ID key Score 2 1 Positive 2 N/A 2 Positive 2 N/A 3 Negative 3 3 Negative 4 4 5 Non-conclusive 5 Non-conclusive run; /* Step 1: Flagging numeric variable in Table 2 */ data table2_flagged; set table2; if not missing(Score) then Score_Flag = 1; else Score_Flag = 0; run; /* Step 2: Flagging categorical variable in Table 3 */ data table3_flagged; set table3; if not missing(Score2) then Score2_Flag = 1; else Score2_Flag = 0; run; /* Step 3: Aggregating flags for Table 2 */ proc sql; create table table2_agg as select ID_key, max(Score_Flag) as Score from table2_flagged group by ID_key; quit; /* Step 4: Aggregating flags for Table 3 */ proc sql; create table table3_agg as select ID_key, max(Score2_Flag) as Score2 from table3_flagged group by ID_key; quit; /* Step 5: Merging all tables */ proc sql; create table final_table as select ref.ID_key, ref.Minutes, ref.Hospital, coalesce(t2.Score, 0) as Score, /* Default to 0 if missing */ coalesce(t3.Score2, 0) as Score2 /* Default to 0 if missing */ from reference_table as ref left join table2_agg as t2 on ref.ID_key = t2.ID_key left join table3_agg as t3 on ref.ID_key = t3.ID_key; quit; /* Step 6: Adding dependent flags (example) */ data final_table; set final_table; if Score = 1 and Score2 = 1 then Valid_Flag = 1; else Valid_Flag = 0; run;   To tackle this problem, you can approach it step by step by: Creating flags in individual tables for numeric and categorical variables. Merging the tables carefully to ensure the duplication issue doesn't inflate the number of records. Combining the results to achieve your desired "want" table with the valid records marked by flags.     ... View more

To address your problem and calculate the time difference correctly, you should convert the hours and minutes into the HH:MM 24-hour format, and then convert these time strings into actual SAS time values. Once you have the time values, you can easily calculate the difference between 'bedtime' and 'time to get up'. Here’s a step-by-step solution: Concatenate Hours and Minutes: You need to format your hours and minutes properly to ensure that they have two digits, particularly for values like "5" minutes, which should be "05". Convert the Time into SAS Time Format: Once you have the time in HH:MM format, you can use the INPUT function to convert it into a SAS time value. Calculate Time Difference: After converting the times, you can subtract them to find the difference, and format the result in hours and minutes. Here’s a code example that does this: /* Combine hours and minutes into HH:MM format for Bedtime */ BT = cats(put(C3_0, z2.), ":", put(C3_1, z2.)); /* Combine hours and minutes into HH:MM format for Time to get up */ TGU = cats(put(C3_2, z2.), ":", put(C3_3, z2.)); /* Convert the time strings into SAS time values */ BT_time = input(BT, time5.); TGU_time = input(TGU, time5.); /* Calculate the time difference, adjust for crossing midnight */ if TGU_time >= BT_time then Time_Diff = TGU_time - BT_time; else Time_Diff = (TGU_time + '24:00't) - BT_time; /* Format the result as hours and minutes */ format BT_time TGU_time Time_Diff time5.;  Explanation: Concatenation: cats(put(C3_0, z2.), ":", put(C3_1, z2.)) ensures that hours and minutes are always two digits. SAS Time Value: input(BT, time5.) converts the concatenated string ( HH:MM ) into a SAS time value. Time Difference Calculation: If the time to get up is earlier than bedtime (crossing midnight), the code handles that by adding 24 hours ( '24:00't ). Formatting: The result is formatted in the HH:MM time format using the time5. format. This will give you the correct output with the time difference calculated in HH:MM .   ... View more

You can create a macro loop in SAS that dynamically selects the relevant previous 12-month datasets based on the month in the base dataset. Here's an example that should guide you in creating the logic: Assumptions: You have monthly datasets named acctJan22 , acctFeb22 , ..., acctDec23 . You want to join these datasets with the current month’s base data to get columns like balance and buckets for each of the previous 12 months. Explanation: base_data: The dataset with your 9k records for 2023. month_list: Extracts distinct months from the base dataset. Looping: For each month in the base dataset, it calculates the previous 12 months and joins the relevant datasets. Join Logic: For each of the 12 previous months, it left joins the corresponding dataset ( acctJan22 , etc.) and appends the balance and bucket information. You can adjust this further based on the specific column names and structures of your datasets. This should help you automate the process of joining the last 12 months' data for each record in your base data.   %macro join_previous_months(base_data=, start_year=2022, start_month=1, num_months=12); /* Loop through each record in your base data */ proc sql noprint; select distinct put(year_month, yymmn6.) into :month_list separated by ' ' from &base_data; quit; %let total_months = %sysfunc(countw(&month_list)); /* Loop through each month in the base data */ %do i = 1 %to &total_months; %let current_month = %scan(&month_list, &i); /* Extract year and month */ %let current_year = %substr(&current_month, 1, 4); %let month_num = %substr(&current_month, 5, 2); /* Create temporary datasets for previous 12 months */ data temp_&current_month; set &base_data(where=(year_month = "&current_month")); %do m = 1 %to &num_months; /* Calculate month and year for the previous months */ %let prev_month = %eval(&month_num - &m); %if &prev_month <= 0 %then %do; %let prev_year = %eval(&current_year - 1); %let prev_month = %eval(12 + &prev_month); %end; %else %do; %let prev_year = &current_year; %end; /* Zero-pad month */ %let prev_month = %sysfunc(putn(&prev_month, z2.)); /* Join previous month's data */ proc sql; create table merged_&current_month as select a.*, b.balance as balance_&m, b.buckets as buckets_&m from temp_&current_month as a left join acct&prev_month.&prev_year as b on a.acct_id = b.acct_id; quit; /* Set temp_&current_month to the newly merged dataset */ data temp_&current_month; set merged_&current_month; run; %end; run; %end; /* Merge all months into a final dataset */ data final_output; set temp_:; run; %mend join_previous_months; /* Example Usage */ %join_previous_months(base_data=base_data_2023, start_year=2022, start_month=1, num_months=12);   ... View more

data SDpost; set a; by id; length PostStroke_S PostStroke_D $20; /* Initialize variables */ PostStroke_S = ' '; PostStroke_D = ' '; /* Logic for Seizure */ if First_Seizure ne . then do; /* Check if seizure occurred before any stroke event */ if (not missing(First_Ischemic) and First_Seizure < First_Ischemic) or (not missing(First_Hemorrhagic) and First_Seizure < First_Hemorrhagic) then PostStroke_S = 'No'; else PostStroke_S = 'Yes'; end; /* Logic for Dementia */ if First_Dementia ne . then do; /* Check if dementia occurred before any stroke event */ if (not missing(First_Ischemic) and First_Dementia < First_Ischemic) or (not missing(First_Hemorrhagic) and First_Dementia < First_Hemorrhagic) or (not missing(R_Dementia_12) and R_Dementia_12 < First_Ischemic) or (not missing(R_Dementia_12) and R_Dementia_12 < First_Hemorrhagic) then PostStroke_D = 'No'; else PostStroke_D = 'Yes'; end; run; ... View more

You can use the NOPRINT option in PROC FREQ along with the OUT= option to store the frequencies in a dataset and then view the total number of rows: proc freq data=have noprint; tables case_ID / out=freq_count; run; proc print data=freq_count (obs=1); var count; run;     NOPRINT : This suppresses the output of the frequency table. OUT=freq_count : This creates a dataset freq_count that contains the frequencies. PROC PRINT : This will print only the total count of case_ID .   ... View more

To replace the 3200 with the value stored in the macro variable &xbarmac , you can use the following approach. This involves dynamically constructing the array declaration with the macro variable: Calculate the Maximum Number and Store in a Macro Variable: You've already done this step correctly: data _null_; set have; call symput("xbarmac", max_number); run; %put &xbarmac;   Use the Macro Variable in the Array Declaration: You can now use the macro variable &xbarmac in the array declaration: data want(drop=day366-day&xbarmac); set have; array daydummy(*) day1-day&xbarmac; /* Your code logic here */ run; Explanation: day366-day&xbarmac : This will dynamically drop the range of variables from day366 to day3562 (if &xbarmac is 3562). array daydummy(*) day1-day&xbarmac; : This will create an array daydummy that includes all variables from day1 to day3562 .   ... View more

The code snippet you've provided is creating a custom format in SAS, and I'll walk you through each step. Step-by-Step Explanation Creating the Dataset disp : data disp; set trcb.DispositionCode_Xref; length start $2 label $100; fmtname = '$disposition'; start = Disp_Code; label = Disp_Code_Desc; keep start label fmtname; run; data disp; : This starts a new dataset named disp . set trcb.DispositionCode_Xref; : This reads in data from an existing dataset called trcb.DispositionCode_Xref . length start $2 label $100; : This sets the length of two new variables: start (length 2 characters) label (length 100 characters) fmtname = '$disposition'; : This assigns the value '$disposition' to a new variable called fmtname . This variable will be used to define the name of the custom format. start = Disp_Code; : This assigns the value from the variable Disp_Code to the start variable. In the context of creating formats, start represents the values that will be formatted. label = Disp_Code_Desc; : This assigns the value from the variable Disp_Code_Desc to the label variable. The label represents the formatted output that corresponds to the start value. keep start label fmtname; : This keeps only the start , label , and fmtname variables in the final disp dataset, discarding any others from the original dataset. Summary of this step: You're creating a new dataset called disp that contains three variables: start (the value to format), label (the formatted output), and fmtname (the name of the format). Sorting the Dataset: proc sort data=disp nodupkey; by start; run;   proc sort data=disp nodupkey; : This sorts the disp dataset by the start variable. The nodupkey option removes any duplicate records based on the start variable, ensuring that each value to be formatted is unique. by start; : This specifies that the sorting should be done by the start variable. Summary of this step: You're ensuring that the dataset disp is sorted by the start values and that there are no duplicate start values. What Does This All Mean? This code is preparing a dataset that will be used to create a custom format in SAS. A format in SAS is a way to map values (like Disp_Code ) to corresponding labels (like Disp_Code_Desc ), so that when you apply this format to a variable, the value gets displayed as the label. In this specific case, a custom format called '$disposition' is being created, which will map each Disp_Code to its corresponding Disp_Code_Desc . Final Step (Not in the Code) Typically, after preparing this dataset ( disp ), you would use the PROC FORMAT procedure to actually create the format from this dataset:   proc format cntlin=disp; run;   proc format cntlin=disp; : This tells SAS to create a format based on the disp dataset you've created. Once this is done, you can use the '$disposition' format to display Disp_Code values as their corresponding Disp_Code_Desc labels in reports or datasets.       ... View more

The +1 in the week definition is indeed meant to add an additional week to the current week number. This is typically used to create a label or identifier for the next week, as in the example wk36 when the current week is wk35 . However, since 2024 has 52 weeks, adding 1 to the last week of the year ( week 52 ) would result in week 53 , which might not be valid depending on how your system or application handles week numbers. If week(date) returns 52 and you add 1, it might result in an invalid week number, leading to potential issues. If the +1 is not intentional (e.g., if you don't actually need to reference the next week), it might indeed be a copy-paste mistake. You could consider removing the +1 if your intention is to use the current week number without incrementing it. ... View more

The hex code for a Form Feed is '0C'x not FF.  Perhaps the photograph you showed was of some tool that displayed the Form Feed character as the little FF character.   So if you are reading in an text report, like a SAS log file, that has form feed characters at the start of each new page then you should be able to remove them.  For example just using the COMPRESS() function.  But you might also want to count them. data want; infile 'myfile.txt' truncover; retain page 1; input line $char133.; if line=:'0C'x then page+1; line=compress(line,'0C'x); run; ... View more

Here's a minor tweak on @FreelanceReinh 's suggestion.  This code avoids reading through the matching observations before accepting the "extras".  Instead, it uses "firstobs=" to jump directly to the extra obs in the longer dataset:   data _null_; if 0 then set BAU_0000_1_CASH_COLLECTED nobs=n1; if 0 then set BAU_0000_2_CASH_COLLECTED nobs=n2; if n1^=n2; fobs=min(n1,n2)+1; call execute ('data want;'); call execute (catx(' ','set',ifc(n1>n2,'BAU_0000_1_CASH_COLLECTED','BAU_0000_2_CASH_COLLECTED'),'(firstobs=',fobs,');')); call execute ('run;'); run; ... View more

This is a great question that touches on the nuances of SDTM (Study Data Tabulation Model) terminology. 1. Record Qualifiers in DM: The variables RFSTDTC , RFXSTDTC , RFENDTC , and RFXENDTC in the Demographics (DM) domain are referred to as "Record Qualifiers" because they define key aspects of the subject’s record that are critical to interpreting the entire dataset for that subject. In the context of DM, these variables provide important temporal references: RFSTDTC: Reference Start Date/Time. RFXSTDTC: Randomization Date/Time (Start). RFENDTC: Reference End Date/Time. RFXENDTC: Randomization Date/Time (End). These dates are central to defining the study period and key events such as randomization for each subject. Since the Demographics domain serves as a summary of each subject's participation in the study, these dates are essential for interpreting the entire record and are therefore termed "Record Qualifiers." 2. Timing Variables in Other Domains: On the other hand, __STDTC variables (like AESTDTC in the Adverse Events domain or LBSTDTC in the Laboratory domain) are called "Timing Variables" because they provide the timing of specific events or observations within those domains. These timing variables help determine when an event (e.g., an adverse event) occurred or when a measurement (e.g., a lab test) was taken. Unlike the DM domain, where the focus is on defining the overall participation timeline for the subject, the purpose of timing variables in other domains is to provide context for individual records or observations. They are critical for understanding when specific data points were collected but are not central to defining the subject's record as a whole. In Summary: Record Qualifiers (DM domain): Key dates that qualify the entire record for a subject, providing crucial context for interpreting the subject’s participation in the study. Timing Variables (Other domains): Dates that provide context for specific events or observations, helping to understand the timing of individual records. Understanding this distinction is important for correctly interpreting the SDTM datasets and ensuring proper data analysis. I hope this clarifies the difference! Please feel free to ask if you have further questions. Best regards, Sarath ... View more