As Quentin suggested, regex replace will do it easily. data have; infile cards; input text &:$50.; cards; Influenza Invalid,FLU-22 Invalid Influenza Invalid(20),FLU-22 Invalid(2999) Influenza Canceled(77),FLU-22 Canceled(77) Influenza Negative,FLU-22 Positive ; run; data want; set have; * lazily search for matching pair of parentheses and replace with empty string; text_clean = prxchange('s/\(.*?\)//', -1, text); run; ... View more

This is an example of how I control the cell width when using ODS EXCEL:   define business_name / 'Business Name' style(column)=[cellwidth=1.25in]; ... View more

In a Teradata database, spool is used whether you query a table or a view, so your statement is not true in this case. ... View more

You can use both a monthly or semimonthly intervals for calculating a midpoint in the month with INTNX() function. For a monthly interval, the alignment option of 'M' uses the 15th day as the midpoint. For semimonthly interval, the period is half-month with starting points on the 1st and 16th days of the month, so you can use the appropriate increment and alignment to get the right dates.   Here's one option:   data _null_; dt = today(); format beg_month mid_month end_month yymmdd10.; * uning monthly interval; beg_month = intnx('month', dt, 0); mid_month = intnx('month', dt, 0, 'm'); end_month = intnx('month', dt, 0, 'e'); put beg_month= mid_month= end_month=; * using semimonth interval; beg_month = intnx('semimonth', dt, -1); mid_month = intnx('semimonth', dt, 0); end_month = intnx('semimonth', dt, 0, 'e'); put beg_month= mid_month= end_month=; run;   Output:   beg_month=2022-04-01 mid_month=2022-04-15 end_month=2022-04-30 beg_month=2022-04-01 mid_month=2022-04-16 end_month=2022-04-30 ... View more

As I mentioned, he doesn't give enough information to pinpoint the issue. You think it may be that the format is not applied to the SAS data, and, therefore, doesn't get properly created on the DB-side. I suggest that the format is properly applied but not recognized by the SAS/ACCESS engine to properly create it on the DB-side. My suggestion is based on Teradata stating that any other numeric format that is not listed results in a float.   Numeric all other numeric formats FLOAT   ... View more

You don't give enough information to fully understand the problem, but I can see from the format you gave that it may cause a faulty data conversion to the SQL DB.    If you are using SAS/ACCESS to <name your relational database> to interface with the DB, SAS makes an educated guess what data type to create on the DB-side based on the SAS data type and format. Since SAS only has 2 data types (numeric and character), it also uses the format to give additional clues for granularity. If you look at the documentation for SAS/ACCESS, it will tell you the formats it recognizes to properly create the SQL DB data type and perform the conversion.   You didn't specify which SQL DB you are working with, but I checked a couple of the ones I use mostly (Teradata and MS SQL Server) and the e8601da10. format is not listed as one of the formats for creating date data type on DB-side. You may want to try date. or datetime. Better yet click the link I provided and check out your specific DB. ... View more

So you just want the last non-null value as the first value. You are not concerned about the order of the remaining values.   data want; set have; array val(*) val1-val8; do _i = 1 to 8; if val(_i) = 'null' then do; * swap the last non-null value with the first; _tmp = val(_i-1); val(_i-1) = val1; val1 = _tmp; leave; end; end; drop _:; run; ... View more

There are some errors in your want output. Anyway to get the correct cumulative counts, use a multilabel format with proc summary.   data have; infile cards dlm='09'x; input ID Age_Group $ Income; cards; 1 21-30 1000 2 51-60 1200 3 31-40 1400 4 51-60 2020 5 21-30 3000 6 51-60 3000 ; run; proc format; value inclvl (multilabel) low - 1000 = '<= $1000' low - 2000 = '<= $2000' low - 3000 = '<= $3000' ; run; proc summary data=have nway completetypes; class age_group; class income / mlf order=formatted; format income inclvl.; var id; output out=want (drop=_:) n=cum_count; run;       ... View more

Here's a data step solution.   data want; set have; active_cum_sum = 0; do _i = 1 to _n_; set have (keep=expirydate amount rename=(expirydate=_expdt2 amount=_amt2)) point=_i; if startdate < _expdt2 then active_cum_sum + _amt2; end; drop _:; run; ... View more