About TylerP8813

TylerP8813 · ‎11-29-2022

Nevermind. I was able to use distinct in the select statement to remove the extra rows and a %SYMDEL step at the end of the macro to clear the macro values.

TylerP8813 · ‎11-28-2022

Ok, so I tried that approach and a few unexpected things happened in my output. I put the Numerator and the Denominator each into their own Macro. For one, I got about the same number of rows for the resultant table that printed out as there are records in the base table (about 7000). Also, the values for each of the two text Macros (Numerator and Denominator) were set to the values from the initial iteration of the report macro and were not overwritten in subsequent iterations of the report macro. Thanks in advance for any further help.

TylerP8813 · ‎11-28-2022

Thanks! Combining it all into one Proc step did the trick! You wouldn't happen to know an easy way to add in a quick comparison percentage would you? Every first select statement is a numerator and every second select step is a denominator.

TylerP8813 · ‎11-23-2022

Currently, I am generating a report on a singular worksheet using ODS Excel Output with Proc Sql steps that all query the same flat file and create about 15 single row "tables" Code example below. I'm trying to convert it to a macro that will run all of the same proc sql steps but create unique tabs for each value as assigned from a separate table. When it runs, it either puts all the resulting tables on one tab (if sheet_interval="none") or one proc result on each tab if I specify another option. I want the macro to run all the proc sql steps per the passed value and then create a new worksheet tab with the new passed value. Example Code: proc sql; create table PassedValue as select distinct PassedValue from All_Clients order by PassedValue; quit; ods excel file="file location/File I want.xlsx"; %macro PM_Measure(PassedValue = ); ods excel options(Sheet_Interval='none' Sheet_Name="&PassedValue." embedded_titles="YES" embedded_footnotes="YES"); title 1 "Title 1"; title3 "Title 3"; footnote2 "footnote 2"; Proc Sql; select count(Value1) from All_Clients where Condition1="YES" and Condition2="YES" and PassedValue="&PassedValue."; quit; ....... (14 more versions of similar step) %mend; data _null_; set PassedValue; str= catt('%PM_Measure(PassedValue=', PassedValue, ');'); call execute(str);run; ods excel close;

TylerP8813 · ‎08-16-2022

This appears to be the right solution but I keep getting this error: "ERROR: CLI prepare error: [Cloudera][ImpalaODBC] (350) Max query retry limit exceeded. Retry Limit: 5" Which may be due to some settings or issue with driver, etc. rather than SAS itself.

TylerP8813 · ‎08-12-2022

We use this proc sql statement to create newly named versions of the tables in a new library called "issue" so that they can be referenced from our code. I'm not necessarily trying to run a function, just create a copy of the table in a new library that SAS can reference.

TylerP8813 · ‎08-12-2022

I have an ODBC already set up. It works fine for all but the 25 or so tables with names longer than 32 characters. We've been using a pass-through for SQL up to this point. Since we migrated to Impala, I need to know the syntax and any other relevant info for the new pass-through statements.

TylerP8813 · ‎08-12-2022

I'm connected to Impala using an ODBC connection. We also use an autoexecutable file that creates all the libraries we need at SAS startup. Within that autoexecutable, we've been using code to create pass through tables for those with names longer than 32 characters. I'm looking for the proper syntax for creating those pass-through's for impala.

TylerP8813 · ‎08-12-2022

That is what the example I provided does when we were connecting to the SQL environment. However, I'm trying to find the correct syntax for an Impala connection.

TylerP8813 · ‎08-12-2022

Background: We recently migrated from SQL Server to Impala Hadoop Environment. We use Kerberos for authentication. We also use an Autoexec at SAS startup to create our data libraries. Does anyone know the proper syntax for writing a proc sql statement for creating a database object for such an environment in the event the filename exceeds 32 characters? For example, the current proc sql step I use (for our former SQL Server environment) looks like this: proc sql; connect to odbc(DSN=MyDSNName user=ReadUser pw=ReadPass); create table issue.newtable as select * from connection to odbc (select * from dbo.MyGoodnessWhyIsTheNameOfThisTableSoEmbarassinglyLong); disconnect from odbc; quit; And no, changing the table name, at this time, is currently not an option.

TylerP8813 · ‎05-29-2019

One more question if you don't mind: I've added a couple other parameters to populate fields. Two of them (specifically related to character fields containing address info) cause me to receive "ERROR: All positional parameters must precede keyword parameters." Relevant lines of code: %macro request(manufacturer=, invoice_num=,invoice_contact=,address_1=, address_2=); data macro_call; set manufacturers (obs=2); str = catt('%request(manufacturer=', manufacturer,',invoice_num=',invoice_num, ',invoice_contact=',invoice_contact,',address_1=',address_1,',address_2=',address_2,');'); call execute(str); run;

TylerP8813 · ‎05-24-2019

Thanks Again!

TylerP8813 · ‎05-24-2019

Reeza, I followed your example and it seems to be working well. I do have a follow-up question if you don't mind. I am using ods pdf and am inserting titles and footnotes within the macro step. Are you familiar with a way to embed a value in a title similar to the way, for example, you might in a subquery in a proc sql step. For example: title1 j=c "&Manufacturer."; title2 j=left "STATE: Ohio" j=right "INVOICE NBR (optional): &Invoice_Num "; In this example, "Manufacturer" takes the place of "r_make" from your GitHub example. I already have invoice number generated in the original table (the one that the TABULATE procedure in the macro will be pulling data from). I just need to know how to get the value into a title for the corresponding manufacturer.

TylerP8813 · ‎05-15-2019

Thank you Reeza!

TylerP8813 · ‎05-15-2019

I used a similar step to generate the resulting tables i mentioned before. As I'm searching for examples to help guide me, is there any particular phrases that you would recommend so that I'm not endlessly digging through unrelated content?

Online Status	Offline
Date Last Visited	‎12-06-2022 01:37 PM

Re: Multiple Sheet Output ODS Excel with Proc SQL

Re: Multiple Sheet Output ODS Excel with Proc SQL

Re: Multiple Sheet Output ODS Excel with Proc SQL

Multiple Sheet Output ODS Excel with Proc SQL

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Accessing Table in Impala where Table Name > 32 Char

Re: Generate Reports based on Dynamic listing of tables

Re: Multiple Sheet Output ODS Excel with Proc SQL

Re: Multiple Sheet Output ODS Excel with Proc SQL

Re: Multiple Sheet Output ODS Excel with Proc SQL

Re: Multiple Sheet Output ODS Excel with Proc SQL

Multiple Sheet Output ODS Excel with Proc SQL

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Re: Accessing Table in Impala where Table Name > 32 Char

Accessing Table in Impala where Table Name > 32 Char

Re: Generate Reports based on Dynamic listing of tables

Re: Generate Reports based on Dynamic listing of tables

Re: Generate Reports based on Dynamic listing of tables

Re: Generate Reports based on Dynamic listing of tables

Re: Generate Reports based on Dynamic listing of tables