About jacqelynl49

jacqelynl49 · ‎02-23-2018

I know this is a very old thread but it pertains to exactly what my question is today. I have one tab with data and a summary tab with multiple pivot tables all pointing to that data. I have this all automated using an excel template. I need to distribute the file with all the pivot tables already updated. The problem is this piece of the code: put '[pivot.refresh()]'; The first data step works for all pivot tables in column 1. Is there a way to refresh ALL? options noxwait noxsync; data _null_; rc=system('start excel'); rc=sleep(10); run; /* Execute VBA code via SAS DDE - open file, select sheet with pivot, put cursor on pivot table and refresh pivot table */ filename cmds dde 'excel|system'; data _null_; file cmds; put "[open(""\\&path\&f_name..xlsx"")]"; put '[worksheet.activate("Summary")]'; put '[select("R5C1")]'; put '[pivot.refresh()]'; put "[save()]"; put "[quit()]"; run;

jacqelynl49 · ‎02-05-2015

Thanks again to everyone for your help. I have reached out to the tech team here and they advised that there is another table that has the loan numbers already formatted so I can use that in my join! We were not aware of this as the tables we are working with are new to our team. Thanks again!

jacqelynl49 · ‎02-05-2015

NOTE: Table WORK.TEST4 created, with 476272 rows and 2 columns. NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format. NOTE: PROCEDURE SQL used (Total process time): real time 4:19.91 cpu time 6.63 seconds column two is correct with the new query but column one is still blank Can't post actual information. Numbers look like this in their correct format - 0012345678 and 12345678 Thank you all for your continued assistance.

jacqelynl49 · ‎02-04-2015

Sorry - didn't say thank you for your suggestion.. ERROR: Function LTRIM could not be located. ERROR: Expression using equals (=) has components that are of different data types.

jacqelynl49 · ‎02-04-2015

I got the loan_no(s) to show correctly by using this: strip(put(lc.loan_no,10.)) Do you know what is causing the blank column?

jacqelynl49 · ‎02-04-2015

Well it was running so I thought we were going to be okay but no luck. No I have no idea what is happening. Reeza: When I run your code I get back NOTHING in the loan_num column and the loan_no(s) are anywhere from 2 to 8 digits which means the leading zeros in loan_no were dropped. Loan_Num LOAN_NO 75 208 521 950 1115 1131 through to 508353265 proc sql; create table test3 as select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc left join PAPDSY1.master1 m1 ON trim(m1.loan_num) = put(lc.loan_no, Z8. -l) ; quit; Patrick: Your suggestion returns the same as Reeza's. proc sql; create table test3 as select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc left join PAPDSY1.master1 m1 ON left(m1.loan_num) = put(lc.loan_no, 13.) ; quit; :smileyconfused:

jacqelynl49 · ‎02-04-2015

I mentioned in the original post that we are using this implicit sql because there are other tables we have created previously that need to be joined into this query. This is just a small snippet of the entire query as this is showing the exact problem. Thank you!

jacqelynl49 · ‎02-04-2015

Still getting the input function requires a character argument. Loan_no is numeric

jacqelynl49 · ‎02-04-2015

Thank you for your quick response. I ran your suggestion and received this error: ERROR: Expression using equals (=) has components that are of different data types. ERROR: INPUT function requires a character argument.

jacqelynl49 · ‎02-04-2015

Hi The below are just examples to use to ask my question. I am trying to change the below proc sql using ODBC to the one below that due to other fields created in previous code. The loan_num and loan_no are being compared in the final query but proc contents of each table shows: LOAN_NO Num 8 11. 11. Loan_Num Char 13 $13. $13. I have been unable to research and find the syntax to convert the numeric loan_no to character or visa versa. Any help much appreciated! proc sql; connect to odbc (noprompt="Driver={SQL Server}; Server=THIS IS INTENTIONALLY LEFT BLANK FOR PRIVACY REASONS; database=daisy001"); create table test2 as select * from connection to odbc (select m1.loan_num, cast(lc.loan_no as varchar) from cpidata.dbo.tbl14 lc left join daisy001.dbo.master1 m1 on m1.loan_num = lc.loan_no where m1.loan_num = lc.loan_no );quit; I want to code as this: proc sql; create table test1 as (select m1.loan_num, lc.loan_no from PAPCPI.tbl14 lc left join PAPDSY1.master1 m1 on m1.loan_num = lc.loan_no where m1.loan_num = lc.loan_no );quit;

jacqelynl49 · ‎01-08-2015

Yes I am.

Online Status	Offline
Date Last Visited	‎06-04-2020 12:06 PM

Re: Automating Excel pivot table in SAS

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

comparing varchar(13) loan number to numeric (10,) loan number

Re: Automating Excel pivot table in SAS

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

Re: comparing varchar(13) loan number to numeric (10,) loan number

comparing varchar(13) loan number to numeric (10,) loan number

Re: Pivot Tables without HTML?