I have a transactional dataset that effectively shows the items sold in every region with the date it was sold ( 1 row per item sold). I have created a week number variable and want a simple output summary that shows the region name in the rows and week number across the columns. The number of regions increases throughout the year as they make sales so this is run every week.   I've summarised the transactional table using a proc summary   proc summary data=raw_trans nway missing; class region week_no; var widgets; output out=regional_summary (drop=_type_ _freq_) sum=; run;   This gives an output of e.g.:   Region       Week_no        Widgets A                      2                      50 B                       2                     7 B                       3                     5 C                      1                      14 C                      2                      6 C                      3                      4 etc   Once I use a proc transpose: proc transpose data=region_summary out= region_summary1 prefix = W_; by region; id week_no; var widgets; run;   Then I get the final output with Week2 and week3 first because region A didn't have any sales in week 1.  What could I add to my code or is there another function that will ensure the output is always ordered alphabetically for the regions and the weeks are in numerical order across the columns?     Region     W_2       W_3       W_1 A                50 B                7             5 C              14            6                 4 ... View more

I'm using the following code using a sql passthrough but it is not correctly displaying the non ascii characters - see code sample A. But when I run the same code using libnames (much much slower so not an option for me to use when looking at larger timeframes and more countries) the characters all display correctly - see sample code B. It feels I need an extra option or piece of code added - any ideas please from this wonderful group of SAS Whizz's would be very welcome. Thank you   Code A proc sql; connect to mysql as myconn (user=&user. password=&password. server=&server. port=3306 database=core); create table memships as select * from connection to myconn ( select a.invoice_reference, a.invoice_date, a.period_date, a.id_invoice_type, a.id_invoice_status, a.to_id_user, a.from_id_company, g.bu_name, a.total_amount, a.to_tax_reference, a.from_id_org, a.to_id_person, a.to_company_name, a.to_person_name, a.to_address_line1, a.to_address_line2, a.to_address_state, a.to_address_postcode, c.id_payment_status, d.email_address, e.email_address as email_1 from invoice as a left outer join membership_application as b on a.id_membership_application = b.id_membership_application left outer join payment as c on a.id_invoice = c.invoice_id_invoice left outer join user as d on a.to_id_user = d.id_user left outer join person as e on a.to_id_person = e.id_person left outer join org_region as f on a.from_id_org = f.id_region left outer join company as g on a.from_id_company = g.id_company where date(period_date)>= '2023-10-01' and date(period_date) <= '2023-10-31' and f.id_country = 10987 and c.id_payment_status in (1,3) and a.id_invoice_type in (1,2); ); disconnect from myconn; quit;   Code B proc sql; create table memships1 as select a.invoice_reference, a.invoice_date, a.period_date, a.id_invoice_type, a.id_invoice_status, a.to_id_user, a.from_id_company, g.bu_name, a.total_amount, a.to_tax_reference, a.from_id_org, a.to_id_person, a.to_company_name, a.to_person_name, a.to_address_line1, a.to_address_line2, a.to_address_state, a.to_address_postcode, c.id_payment_status, d.email_address, e.email_address as email_1 from core.invoice as a left outer join core.membership_application as b on a.id_membership_application = b.id_membership_application left outer join core.payment as c on a.id_invoice = c.invoice_id_invoice left outer join core.user as d on a.to_id_user = d.id_user left outer join core.person as e on a.to_id_person = e.id_person left outer join core.org_region as f on a.from_id_org = f.id_region left outer join core.company as g on a.from_id_company = g.id_company where datepart(period_date)>= '01Oct2023'd and datepart(period_date) <= '31Oct2023'd and f.id_country =10987 and c.id_payment_status in (1,3) and a.id_invoice_type in (1,2); quit;   I've uploaded a screenshot of the 2 output datasets to show the issue         ... View more

hi there, I'm experienced in creating macros and manipulating arrays etc but not in using %do loops outwith a data step / proc sql so am getting stuck on this.   I have a data set (called masterlist) with 2 variables - varnum is sequential and variable1 has no duplicates - each entry is unique: varnum  variable1 1              CompanyName1 2              CompanyName2 3              CompanyName3   etc N             CompanyNameN   I want to search another table (newtable) to flag all instances of each of the values in variable1 i.e. CompanyName1, CompanyName2 --- CompanyNameN  to find how many of each record in variable1 are in newtable (which has a variable companyname).  The companyname field in newtable is free text so can be entered with capitals, shortened form (LTD instead of Limited), extra spaces, spelling mistakes etc) etc so  it is not always an exact match to what is held in the table masterlist.  I am going to do some "fuzzy" matches based on substring and index functions on key parts of the value in variable1.  I can do what I need for just one value of variable1, but how do I make the program loop through all values in variable1? In simple terms I want something like this:   do i=1 to (N) data newtable; set newtable; if substr(companyname, 1,3) = variable1(i) or index(companyname,"variable1(i)") > 0 then flag = (i); run; end;   Each row in column "flag" would end up with lots of blanks and numbers between 1 and N (the max being the number of entries table masterlist) and then I ultimately want to do a count of each " fuzzy match" on companyname in newtable   proc summary data=newtable nway missing; class flag; output out= etc; run;   I feel I need to create global variables for varnum(i) and variable1(i) and then feed those into a do loop for the data step?   Any suggestions on how to code this would be great as I have not used %do before or generated multiple global macro variables via a do loop either.       ... View more