About SASMom2

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'

SASMom2 · ‎05-27-2021

I need help with an issue I am struggling with. I have a table with two variables. The ID variable is a unique identifier and the Cust_ID field is the customer IDs for the unique ID values. An ID can have one or multiple cust_ids. I want to create a third variable multi_cust_id which lists all cust_id values for each IDs. As you can see in my table below for ID '1' there are two cust_ids so the new multi_cust_id has both cust_ids listed in both lines where ID = 1. They all need a single quote surrounding them and be separated by a comma. So, how do create the multi_cust_id variable? Below is my made up table. Thanks a lot. ID Cust_id multi_cust_id 1 12345 12345', '56789' 1 56789 12345', '56789' 2 23456 23456', '67890', '34567', '78901' 2 67890 23456', '67890', '34567', '78901' 2 34567 23456', '67890', '34567', '78901' 2 78901 23456', '67890', '34567', '78901' 3 35678 35678' 4 54321 54321', '23459', '34557', '54390' 4 23459 54321', '23459', '34557', '54390' 4 34557 54321', '23459', '34557', '54390' 4 54390 54321', '23459', '34557', '54390' 5 34889 34889'

SASMom2 · ‎04-21-2020

Tom, thanks for your reply. I was able to resolve it by doing below. proc sql noprint; select count(*) into :nobs from test where custrun = "&custrun."; quit; %if &nobs = 0 %then %do; %goto exitgoto; %end;

SASMom2 · ‎04-21-2020

I have a code that creates 3,000 individual excel reports through ODS Tagsets in SAS. Some of them are created even if there is not data in the source dataset. So, when you open the excel report there is no data in it. I do not want to create the excel report if there is no data in the dataset. How do I program that? I tired to copy my table but for some reason I could not do it so I do not have any table to show you. Thanks in advance.

SASMom2 · ‎03-12-2020

The code is long but basically, below is calculation. (c.avg_mship*a.acp*a.capture_rate) as Annual_Target

Online Status	Offline
Date Last Visited	‎03-23-2023 02:08 PM

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: ODS Excel

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

Reformatting a field/variable

Re: How to exit if a dataset did not have any records

How to exit if a dataset did not have any records

Re: Rounding a Number