https://studysas.blogspot.com/2024/09/unleashing-power-of-proc-datasets-in.html ... View more

  Using PROC DATASETS to check the SORTEDBY attribute: SAS stores metadata on whether a dataset has been sorted using the SORTEDBY attribute. You can check this metadata with PROC DATASETS .   Copy code In the output, if the sortedby variable has a value, it indicates that the dataset is sorted by the corresponding variables. If the dataset is not sorted, the sortedby variable will be blank.   ... View more

You can also so this using PROC DATASETS and FORMAT You can use PROC DATASETS to modify the dataset without having to explicitly loop through the variables.  Applying Uppercase Formatting to Character Variables In this method, you use the $upcase. format to ensure that all character variables in your dataset are stored in uppercase format. The $upcase. format is predefined in SAS and will automatically convert all character data to uppercase. proc datasets lib=work nolist; modify original_dataset; format _character_ $upcase.; run; quit;   ... 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

Can you try this and see if it works for you.   Corrections made: Replaced curly braces {} with square brackets [] for referencing array elements. Fixed the single quotes around the string constants (changed them from “curly” quotes to straight quotes). Ensured the IN operator was used properly by separating the values with commas and enclosing them in parentheses. This should resolve the syntax errors you were encountering.   Data temp (drop=z); set work.complete22; array DXS[18] $ dx1-dx18; CO_FLAG = 0; do z= 1 to 18; if substr(DXS[z],1,5) IN ('T5801', 'T5804', 'T5811', 'T5814', 'T5891', 'T5894', 'T5802', 'T5803', 'T5812', 'T5813') or substr(DXS[z],1,6) IN ('T582X2', 'T582X3', 'T588X3', 'T5892', 'T582X1', 'T582X4', 'T588X1', 'T588X4') or DXS[z] in ('T5882X2') then CO_FLAG = 1; leave; end; run;     ... 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

Check this one..   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

Can you try....     DATA linhao; INFILE "&sysparm" ENCODING="UTF-8" TRUNCOVER LRECL=341 OBS=1000; INPUT @1 linhao $500.; /* Replace special character (e.g., FF - form feed) with a space */ linhao = TRANSLATE(linhao, ' ', '0C'x); /* '0C'x is the hexadecimal value for form feed */ /* Alternatively, remove the special character */ linhao = COMPRESS(linhao, '0C'x); RUN;   PROC PRINT DATA=linhao (OBS=5); RUN; ... View more