About SASMom2

SASMom2

Thank you all for your help. I used the proc tabulate and works beautifully!

SASMom2

Thanks Ballardw That worked!!! One more question...How do I save this table?

SASMom2

Thanks Tom for your reply. I need to see the 'Percentage of Totals' in a separate column as shown in the original post.

SASMom2

I have a table XYZ and I am running below code to get frequency of two fields in that table. proc freq data = XYZ; tables Pre_intent*T4 / nopercent norow nocol; run; I get below output. Table of Pre_intent by T4 Pre_intent T4 1 ER 2 UC 3 OV Total 1 ER 9 1 176 376 2 UC 26 12 1450 4333 3 OV 7 2 539 1576 Total 42 15 2165 6285 I would like to add a 'percentage of total' field so it would look like below. Table of Pre_intent by T4 Pre_intent T4 1 ER 2 UC 3 OV Total Percentage of Total 1 ER 9 1 176 376 0.05982498 2 UC 26 12 1450 4333 0.689419252 3 OV 7 2 539 1576 0.250755768 Total 42 15 2165 6285 1 Also, how do I save this frequency table as a dataset??? Thanks in advance for all your help!

SASMom2 · ‎03-22-2023

Thank you everyone for your reply. Below statement worked. %if &cust. = 'abc' or &cust. = 'def' %then %do;

SASMom2 · ‎03-22-2023

I need to write a condition macro using %if - %else. Below is an example. I am creating ‘typ’ macro to create a table depending on the values of ‘cust’. If the values are ‘abc’ or ‘def’ then I want to run one set of proc sql else run another set of proc sql. %macro Typ; %if "&cust." = "abc" or %if "&cust." = "def" %then %do; proc sql; quit; %end; %else %do; proc sql; quit; %end; %mend Typ; %Typ; *********************************************************** I tried below %if statements. 1) %if "&cust." = "abc" or %if "&cust." = "def" %then %do; 2) %if "&cust." in ("abc","def") %then %do; When I run the code for cust = def using the 1st %if statement above the log says “&cust.” = “def” is FALSE so it goes to the else part and runs the second set of proc sql. When ran the code using the 2nd %if statement, it does not recognize ‘in’ as an operator. 3) %if "&cust." = 'abc' or %if "&cust." = 'def' %then %do; / *single quotes */ 4) %if "&cust." in ('abc','def') %then %do; /* single quotes */ I tried to run same 1st and 2nd %if statements using single quotes (statements 3 and 4 above) for the ‘cust’ values, it gave error. Please help. Thanks

SASMom2 · ‎04-28-2022

Thanks for your reply. I will let you know if it works.

SASMom2 · ‎04-28-2022

I need help with creating a new field using values in an existing column. I have a dataset with two columns, Customer and Customer_Number. Below is a sample dataset. I need to create a new field Customer_Number_Multi using the values in the Customer_Number column. I also need single quotes around each value and comma as a seperator. As you can see some Customers have one Customer Number and some have multiple Customer Numbers. The Customer_Number_Multi field should have all Customer_Number values for each Customer. So, for an example, for Customer ABC should have all, 123, 234 and 567 Customer_Numbers in Customer_Number_Multi. Thanks for your help. Customer Customer_Number Customer_Number_Multi ABC 123 '123', '234', '567' ABC 234 '123', '234', '567' ABC 567 '123', '234', '567' DEF 321 '321' GHI 345 '345', '678' GHI 678 '345', '678' JAK 256 '256'

SASMom2 · ‎07-27-2021

Thank you all for your reply. I added the word 'label' which worked. I have to try it with some other reports to make sure it works for them because even with the 'label' after NOOBS, it was not working previously. Thank again for your prompt replies.

SASMom2 · ‎07-27-2021

I will send you the log and output but to reply to your question about the error, it does not give any error but when I look in to the excel, the fonts are not what I asked it to use, column headings has original variable names. It did not use the 'label'.

SASMom2 · ‎07-27-2021

All of our reports use ODS Tagsets (proc print or proc report) to send tables from SAS to Excel in XLS format. We are thinking to transfer them in XLSX (workboook) format because sometimes it takes forever to open/transfer large XLS files. In our experience, XLSX is quick to open and transfer. We use 'label' to give them appropriate headers in the proc print/proc report. I tried to use 'label' for ODS Excel but it did not work. I think it's because ODS Excel does not support Tagsets??? I am not sure. For that matter, I do not think it support any Options/Styles etc. Does anyone know how to go around this so I can use 'label' in XLSX format??? Obviously, I could label each variable in proc sql before I get to ODS Excel but that would be too much work for the number of reports we have. Below is an example of a piece of a code. ods Excel close; ods Excel style=Analysis file="&path./report..xlsx" options(sheet_name='Counts' autofilter='none' orientation='landscape' absolute_column_width = '20,20,15,20,23,21' row_heights = '15,15'); proc print data=Final noobs style(header) = [ font_style=italic font_weight = bold font_size = 10pt just = center vjust = center foreground = black background = #CCCCFF] style(column) = [ font_size = 9pt]; label clientname='Client Name' clientnumber = 'Client Name' ID = 'ID' Count1='Count1' Count2='Count2' percent='Percent'; run; ods Excel close;

SASMom2 · ‎05-27-2021

Thanks you all for your reply. You saved me a lot of manual work in Excel. I ended up using Tom's solution. It gave me exactly what I needed. Thank you!

SASMom2 · ‎05-27-2021

Basically, the muti_cust_id field is for reporting purpose only. At the end of the code when it creates an excel report, it lists cust_id fo each id. So, if we use cust_id field, for the ids that have mutiple cust_ids, there would be multiple lines. But if we use multi_cust_id field, there would be only one line per ID regardless on cust_ids linked to each ID. Hope that makes sense.

SASMom2 · ‎05-27-2021

I think it would work. I ran it and I think I will use the option 2. I just have to try it out using my table. Thanks again for your help. I will post the update.

SASMom2 · ‎05-27-2021

Thanks Reeza I changed the ID values to texts. All three variables are text/string variables. Below is slightly revised table. ID Cust_id multi_cust_id ABC 12345 12345', '56789' ABC 56789 12345', '56789' DEF 23456 23456', '67890', '34567', '78901' DEF 67890 23456', '67890', '34567', '78901' DEF 34567 23456', '67890', '34567', '78901' DEF 78901 23456', '67890', '34567', '78901' HIJ 35678 35678' KLM 54321 54321', '23459', '34557', '54390' KLM 23459 54321', '23459', '34557', '54390' KLM 34557 54321', '23459', '34557', '54390' KM 54390 54321', '23459', '34557', '54390' NOP 34889 34889'

Online Status	Offline
Date Last Visited	2 weeks ago

Re: Proc Freq Question

Re: Proc Freq Question

Re: Proc Freq Question

Proc Freq Question

Re: Macro Question

Macro Question

Re: Creating a new field

Creating a new field

Re: ODS Excel

Re: ODS Excel

Re: ODS Excel

Re: Proc Freq Question

Re: Proc Freq Question

Re: Proc Freq Question

Proc Freq Question

Re: Macro Question

Macro Question

Re: Creating a new field

Creating a new field

Re: ODS Excel

Re: ODS Excel

ODS Excel

Re: Reformatting a field/variable

Re: Reformatting a field/variable

Re: Reformatting a field/variable

Re: Reformatting a field/variable