About Jyuen204

Jyuen204 · ‎09-17-2020

Here is an example. So Table1 is the daily list of records that can vary day to day. This example has 3 records (Cust1, Cust2, Cust3). I want to assign a value from Table2.CODES to Table1.CODES. data table1; Length CustID $5; input CustID $ CODES; datalines; Cust1 Cust2 Cust3 run; data table2; input CODES; cards; 123 456 789 101 111 121 run;

Jyuen204 · ‎09-17-2020

I have 2 tables Table 1 has data that will get refreshed daily so 1 day could have 3 records, next day could have 5 Table 2 has 250 static values I want to update Table 1 with a value for each row from Table 2. Table 2 has no matching value to Table 1. I just want to assign one of the values to each record in Table 1

Jyuen204 · ‎09-02-2020

Sorry I'm not really following. when I use the below code, the first field comes back with correct date, but the second field using the datetime19. does not. proc sql; select &CURRENT_DATE format=mmddyy10. as CREATION_DATE_TIME, &CURRENT_DATE format= datetime19. as CREATION_DATE_TIME from BI9146_TMP; run;

Jyuen204 · ‎09-02-2020

I have the following code I am using and the output for a field I need as Date Time Formating Data DATES; Format CURRENT_DATE EXP_DATE REPORT_DATE date9.; CURRENT_DATE = today(); EXP_DATE = today()+8; REPORT_DATE = intnx('Month',CURRENT_DATE,-1,'E'); Call Symput('CURRENT_DATE',CURRENT_DATE); Call Symput('REPORT_DATE',REPORT_DATE); Call Symput('EXP_DATE',EXP_DATE); In my select statement I use : Select &CURRENT_DATE format=datetime19. as CREATION_DATE_TIME but instead of todays datetime, I am getting "01JAN1960:06:09:20" What am I doing incorrectly? if I use format = mmddyy10. I get the right date, just not the proper format I am after.

Jyuen204 · ‎09-01-2020

Thanks i will give that a try. I used Compress() and that seems to have done the trick as well. Always good to have multiple solutions! Cheers everyone!

Jyuen204 · ‎09-01-2020

Would i just be using the field names ? _1 = CUST_ID etc?

Jyuen204 · ‎09-01-2020

My end result that i want would be CUST-123652357422159 6523574 is just an arbitrary number in my data

Jyuen204 · ‎09-01-2020

I have a data set that has been compiled and I have to concatenate 3 of the fields. 1 field is a character field : CUST-123 2nd Field is numeric: 56789 3rd field is a date field: 01/Sep/2020 proc sql; select CUSTID||''||put(NUMBER_ID,10.)||''||put(CREATION_DATE_TIME,10.) from TABLE; run; Using the Put it changes the 2 non character fields to character but my result set comes out as: CUST-123 6523574 22159 I tried trim but that doesnt work. I want the result set but without any spaces

Jyuen204 · ‎06-10-2020

Thank you Thank you!

Jyuen204 · ‎06-10-2020

I have a volatile table built wilh a set of values ID XDATE 123 02APR2020 234 06FEB2020 234 03MAR2020 ..... I am querying the Max date grouped by the ID PROC SQL; select ID, MAX(XDATE) as XDATE from TABLE1 group by ID; run; I get the result as ID XDATE 123 22007 234 22046 345 21970 How do i get a date output like '12-31-2999'

Jyuen204 · ‎03-11-2020

Thanks so much!

Jyuen204 · ‎03-11-2020

Apologies for the rather simplistic question. But I have a date set I generate in the code to reference. REPORTDATE = intnx('Week',today(),-1,'E'); CW_END = intnx('Week',ReportDate,0,'E'); This gives me the most recent Saturday that passed for a report I generate. I need to be able to get this exact Date in the previous year. So in this case March 7 2020 is the date generated from above code. I need to get March 7 2019. I would use a hard coded calculation but that would mess up in Leap years like this year. Thanks in advance!

Jyuen204 · ‎11-22-2019

If I change it to an XML file format, it sends me a file with *.xml and I do not want that either. I tried using ODS EXCEl instead of ODS EXCELXP and that did not work and still generated the same error. I would use the XLSX option but I do not know why all of my formatting options are thrown out the window. Unless there is a completely different set of syntax required to format each column differing from what i coded for XLS...

Jyuen204 · ‎11-22-2019

in Addition, I have tried the XLSX in the code below. And while it does not produce the errror as with the CLS option, my column and tab formatting are all off wack. I used ABSOLUTE COLUMN WIDTH for sizing the columns in each report tab to be a specific size. however with the xlsx format it seemingly ignores all of my entries and also Bolds my tab titles which i never asked it to do %let report_dir = %sysfunc(pathname(work)); ods excel file = "&report_dir./Bell MTS - Nordia Report Package.xlsx"; ods tagsets.ExcelXP path= "&report_dir" file= 'Bell MTS - Nordia Report Package.xls' Style=Printer; ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Daily Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9,9,9,9,9'); %report_page1(Nordia_Daily); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9'); %report_page2(Nordia_Weekly); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Monthly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9'); %report_page3(Nordia_Monthly); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Week over Week % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9,9,9,9,9'); %report_page4(NORDIA_WEEKLY_PCT); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month over Month % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9'); %report_page5(NORDIA_MONTHLY_PCT); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month to Date Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9'); %report_page6(NORDIA_MTD_TRANSFERS); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Agent Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='23,20,6.5,6.5,6.5,6.5,6.5,6.5'); %report_page7(AGENT_REPORT); ods tagsets.ExcelXP close;

Jyuen204 · ‎11-22-2019

I am creating an excel export and am running into a semi-error. the below is my export and i am exporting to and XLS format. THe below export code exports based on my report macros. The output is exaclty as I want the report on every tab. However when It exports and emails to my distribution (email string not included) ; When I open the file attachment I get the following message: "The file format and extension of 'Report Package (034).xls' don't match. The file could be corrupt or unsafe ....." The file opens just fine and looks fine in all aspects. I just do not know why it would give this message when i open the excel document %let report_dir = %sysfunc(pathname(work)); ods tagsets.ExcelXP path= "&report_dir" file= 'Report Package.xls' Style=Printer; ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Daily Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9,9,9,9,9'); %report_page1(Nordia_Daily); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9,9,9,9,9'); %report_page2(Nordia_Weekly); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Monthly Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='43,9,9,9,9,9,9'); %report_page3(Nordia_Monthly); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Week over Week % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9,9,9,9,9'); %report_page4(NORDIA_WEEKLY_PCT); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month over Month % Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9,9,9,9,9'); %report_page5(NORDIA_MONTHLY_PCT); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Month to Date Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='30,9,9'); %report_page6(NORDIA_MTD_TRANSFERS); ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Agent Summary Report' AUTOFIT_HEIGHT="yes" absolute_column_width='23,20,6.5,6.5,6.5,6.5,6.5,6.5'); %report_page7(AGENT_REPORT); ods tagsets.ExcelXP close;

Online Status	Offline
Date Last Visited	‎04-30-2024 06:23 PM

Re: Referencing a table that has a set of dates in SAS Date format tha...

Re: Referencing a table that has a set of dates in SAS Date format tha...

Referencing a table that has a set of dates in SAS Date format that I ...

Re: Selecting a field that was assigned a numeric heading.

Selecting a field that was assigned a numeric heading.

Re: Data conversion from Date to a Character

Data conversion from Date to a Character

SAS Excell Report - Section Column Breaks

SAS Report Define Cell Alignment

Re: %let If Then Else

Re: Case statement with Dates

Re: 2 Tables - Need to update 1 with values from another

2 Tables - Need to update 1 with values from another

Re: Formating Date Time

Formating Date Time

Re: Conversion of numeric to Character in a concatenate

Re: Conversion of numeric to Character in a concatenate

Re: Conversion of numeric to Character in a concatenate

Conversion of numeric to Character in a concatenate

Re: SAS Date issue

SAS Date issue

Re: Date generation

Date generation

Re: Excel Export Error

Re: Excel Export Error

Excel Export Error