I am looking at 5 classes of psychotropic meds (anti_anxiety, anti_psychotic, anti_depressant, mood_stabilizer, stimulants) over two years 2022 and 2023. The age groups are 0-5, 6-12, 13-17.   Each year was extracted by quarter by the month of the quarter.  So there are 4 groupings for each drug class: anti_anxiety_2022_q1_1           anti_anxiety for 2022 for qtr 1 January anti_anxiety_2022_q1_2          anti_anxiety for 2022 for qtr 1 February anti_anxiety_2022_q1_3          anti_anxiety for 2022 for qtr 1 March                                            . . . anti_anxiety_2022_q4_1           anti_anxiety for 2022 for qtr 4 October anti_anxiety_2022_q4_2          anti_anxiety for 2022 for qtr 4 November anti_anxiety_2022_q4_3          anti_anxiety for 2022 for qtr 4 December   Data for each of the other 4 med classes are have similar construction   The data is cleaned up a little (e.g. if header_amount_paid = 0, record is deleted) Age groupings are added SQL coding is used to create So a hypothetical anxiety quarterly dataset (Q1) looks like   From DOS          Recipient_ID        Total_Paid     Age_group 30Jan22              EO401J5K               132.86            0-5 05FEB22             BY3267G1                 43.55           13-17 28MAR22            PA601M4L               67.73             6-12   The four quarters for each med were set together to give   Anxiety_2022_final Psychotic_2022_final Depressant_2022_final Mood_Stable_2022_final Stimulants_2022_final   The request is to get a count of unique recipients that received all 5 drugs for each age grouping.   My approach was to do the following   Data all drugs_2022;   Set anti_anxiety_2022          anti-psychotic_2022          anti_depress_2022          anti_mood_2022          anti_stimulant_2022; run;   proc sort data=all_drugs_2022                   out=uniquerecipients_2022 nodups  dupout=dup_recipients_2022 nodupkey;                   by Recipient_ID; proc sql noprint; create table unique_rec_meds_2022 AS select  From_DOS, Recipient_ID, Sum(header_amount_paid) AS Total_paid, Age_group, COUNT(Age_group) AS A_grp From all_drugs_2022 Group By age_group; quit;   So the final result would be a excel table (results are hypothetical for illustration)   CY 2022        Age_group       Recipients         Payments 2022                   0-5                     8,235                $1,644,876                             6-12                   41,875             $39,546,233                            13-17                   54,712            $31,876,098 and a similar table for CY 2023 I thought this would produce a table of the number of children who received all psychotropic drugs, by age group, and payments   But the requester doesn't think so.   At this point, I am confused then on how to arrive at the correct result.   Any suggestions and direction will be appreciated.   Thanks.   wlierman           The same final output       data anxiety_2022_q1_1;    set anxiety_2022;           ... View more

This question is building off of a solution from ballardw(Super User) regarding building a proc informat approach to labeling employment titles.   I have many, many lines of type_firm descriptions a sampling will look something like in Excel   Agricultural_worker                         = 2003 Apparel_Manufacturing                   = 2004 Beverage_Manufacturing                = 2002 Cabinets_Woodworking                   = 2005 Construction_Homes_Residential    = 3002 (the codes are what I am using to delineate the different firm types) and so on I guess I have two questions   1) can I just copy and paste these rows into my data step - actually the Proc format (and I have ~ 940 rows and Services will have well over 1500)   and 2   Once step number 1 is successful the should look like   Proc format invalue Firm_type (upcase) Agricultural_worker                         = 2003 Apparel_Manufacturing                   = 2004 Beverage_Manufacturing                = 2002 Cabinets_Woodworking                   = 2005 Construction_Homes_Residential    = 3002   and so on   how would I code so that " " can be added to the start and end of the type firm to look like   "Agricultural_worker"                         = 2003 "Apparel_Manufacturing"                   = 2004 "Beverage_Manufacturing"                = 2002 "Cabinets_Woodworking"                  = 2005 "Construction_Homes_Residential"    = 3002   Another thing just occurred to me the codes on the excel spread sheet look like '= then the no so the ' before the = needs to be removed too.   This is a long convoluted question, but I appreciate your help and assistance.   wlierman ... View more