About new_sas_user_4

new_sas_user_4 · ‎08-07-2019

Hi, I am extracting data using Teradata where the dates are in 01JUN2019:00:00:06.000000 format. - I want to extract data based on a date range (example below) such that I could use a macro like %let M1='2019-06-01' instead of date'2019-06-01' - Also get the day number based on the date...( eg 01JUN2019:00:00:06.000000 would be 01 (day 1)) Example : PROC SQL; CONNECT TO TERADATA(USER="" PASSWORD="" SERVER="" mode=teradata DATABASE=""); create table A as select region, dt, day(cast(dt as timestamp)) as dt_number from connection to teradata (select region, dt from Teradata_table where T_date between date'2019-06-01' and date'2019-06-30' ) ; disconnect from teradata; quit; Below is a sample data I created(though the date section is showing up as null when I run this...should the input statement be modified?) data raw_Data; length region $25; input region $ dt; datalines; British Columbia 01JUN2019:00:00:06.000000 Alberta 02JUN2019:00:00:42.000000 ATlantic 03JUN2019:00:03:23.000000 Saskatchewan 01JUN2019:00:03:28.000000 Quebec 01JUN2019:00:05:06.000000 Manitoba 01JUN2019:00:05:06.000000 Quebec 01JUN2019:00:05:06.000000 Manitoba 01JUN2019:00:05:06.000000 Saskatchewan 01JUN2019:00:05:06.000000 Manitoba 01JUN2019:00:05:06.000000 Saskatchewan 01JUN2019:00:05:06.000000 ; Help please!

new_sas_user_4 · ‎07-18-2019

I think the solutions with intnx will work for my query... I didnt understand this part @novinosrin : data want ; if _n_=1 then do; dcl hash H (dataset:'look_up') ; h.definekey ("month","year") ; h.definedone () ;

new_sas_user_4 · ‎07-18-2019

This works fine, But I want to automate it so the next month ...say August 2019 I wont have to re-write : Proc sql; create table A as select * from B where (month in (1,2,3,4,5,6,7) and year in (2019)) or (month in (8,9,10,11,12) and year in (2018)); run; The values for month and year should be filled in automatically in the code based on the month I am in!

new_sas_user_4 · ‎07-18-2019

Hi Everyone, I want number series for the past one year for subsetting data: Eg: If I am in July, I want to get the past 12 months data. Proc sql; create table A as select * from B where (month in (1,2,3,4,5,6) and year in (2019)) or (month in (7,8,9,10,11,12) and year in (2018)); run; The month and year fields are numeric in table "B". How can this be done? Thank you 🙂

new_sas_user_4 · ‎07-12-2019

my bad 😞 I was using the wrong dataset. Your solution is perfect ! Thanks a ton @novinosrin 🙂

new_sas_user_4 · ‎07-12-2019

I want the freq count of the subs which exist in test. I want the freq count from List. using the solutions you gave me @novinosrin , I get the below result: 100 1 102 1 103 0 33 0 90 0 I want : 100 1 102 1 103 0 90 3 33 0

new_sas_user_4 · ‎07-11-2019

This is what I get when I run the above code: 100 3 102 3 102 3 33 3 90 3 I want this: 100 1 102 1 103 0 90 3 33 0

new_sas_user_4 · ‎07-11-2019

Sorry about that! Data Test; input Sub1 $ value $; cards; 100 A 102 B 103 C 90 D 33 E ; run; Data List; Input Sub2 $; cards; 100 90 90 90 102 ; Run; Desired Output (Get the frequency of Sub1 if it exists in Dataset List): Sub1 Count 100 1 102 1 103 0 90 3 33 0

new_sas_user_4 · ‎07-11-2019

Hi Everyone, Could you please help with below: If the variable Sub1 from dataset "Test" exists on dataset "List", I want to know its frequency(from dataset List). The variable sub1 and sub2 are char type(default.) Data Test; input Sub1 $ value $; cards; A 100 B 102 C 102 D 90 E 33 ; run; Data List; Input Sub2 $; cards; 100 90 90 90 102 ; Run; Thank you 🙂

new_sas_user_4 · ‎06-10-2019

Thanks a lot Cynthia 🙂 This is perfect!!

new_sas_user_4 · ‎06-08-2019

Thanks Cynthia ! Below is the sample data and code: data raw_data; length metric $7 dimension1 $12 ; input metric $ dimension1 $ Col_A Col_B Col_C Col_D Col_E Col_F Col_X Col_Y Col_Z ; datalines; Ontario Total 100 0.67 0.70 400 100 250 50 500 0.21 Alberta Total 70 0.65 0.56 200 60 150 40 400 0.23 Ontario Total_split1 40 0.51 0.75 250 70 140 40 300 0.22 Alberta Total_split1 30 0.45 0.72 120 30 120 10 130 0.26 Ontario Total_split2 60 0.37 0.75 150 30 110 10 200 0.23 Alberta Total_split2 40 0.29 0.77 80 30 30 30 270 0.04 Ontario Total_splitA 20 0.21 0.80 100 30 90 10 120 0.25 Alberta Total_splitA 10 0.13 0.82 90 10 50 10 100 0.04 Ontario Total_splitB 20 0.05 0.85 150 40 100 10 130 0.26 Alberta Total_splitB 20 0.45 0.87 80 20 60 12 70 0.86 Ontario Total_splitC 50 0.85 0.90 30 10 10 20 100 0.35 Alberta Total_splitC 30 1.25 0.92 10 12 15 5 80 2.82 Ontario Total_splitD 10 1.65 0.95 -50 -10 50 10 150 0.56 Alberta Total_splitD 10 2.05 0.97 20 18 25 13 150 1.12 ; %let report_dir = %sysfunc(pathname(work)); ods pdf file="&report_dir/PDF_file_draft1.pdf" notoc; proc report data=raw_Data headline headskip style(report)=[cellspacing=0 cellpadding=0.5 frame = box rules=groups font=(Arial, 7pt, bold) WIDTH=100%] style(header)=[foreground=white background=black font=(Arial, 7pt, bold) borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=black NOBREAKSPACE=ON ] style(column)=[font=(Arial, 7pt) foreground=black background=CXFFFFFF JUST = C VJUST=C bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ]; Columns metric dimension1 ("Count" Col_A Col_B Col_C Col_D Col_E Col_F) ("Region" Col_X Col_Y Col_Z); define metric/group order=data; define dimension1 /display ; define Col_A/display format = comma12.; define Col_B/ display format = percentn12.1; define Col_C/display format = percentn12.1 ; define Col_D/format = comma12. ; define Col_E/display format = comma12.; define Col_F/display format = comma12.; define Col_X/display format = comma12.; define Col_Y/display format = dollar12.2; define Col_Z/display format = dollar12.; compute dimension1; if dimension1 = 'Total' then call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1]'); endcomp; compute Col_A; call define(_COL_,'style','style=[borderleftcolor=black borderleftwidth=1]');endcomp; compute Col_X; call define(_COL_,'style','style=[borderleftcolor=black borderleftwidth=1]');endcomp; run; ods pdf close; This produces the below output: I want the output to look like below : - Horizontal line after Total_split2 (not under metric) - Vertical line after the column metric Also, if the first column (Ontario, Alberta) to be centered(like merge and center in excel) 🙂 Thank you !!

new_sas_user_4 · ‎06-07-2019

Hello Everyone, I am preparing my first ever pdf file 🙂 Below are a few questions I have if you could please help out!! I am grouping based on metric..(I could group based on metric AND dimension1 if that helps) Dimension1 has values Total Total_split1 Total_split2 Total_SplitA Total_SplitB Total_SplitC Total_SplitD 1.) I want a line before Total_SplitA but only for the rows under the "count" and "Region" columns..Is there a way to achieve this? 2.) How can I bold the Total row values? 3.) For every group in the metric I want a line which I obtain using (compute dimension1; if dimension1 = 'Total' then call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1]'); endcomp; ) Is there a better way to do this? Below is my code: ods pdf file="&report_dir/PDF_file_draft1.pdf" notoc; proc report data=FILE1 headline headskip style(report)=[cellspacing=0 cellpadding=0.5 frame = boxrules=groups font=(Arial, 7pt, bold) WIDTH=100%] style(header)=[foreground=white background=black font=(Arial, 7pt, bold) borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=black NOBREAKSPACE=ON ] style(column)=[font=(Arial, 7pt) foreground=black background=CXFFFFFF JUST = C VJUST=C bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ]; Columns metric dimension1 ("Count" Col_A Col_B Col_C Col_D Col_E Col_F) ("Region" Col_X Col_Y Col_Z); define metric/group order=data; define dimension1 /display ; define Col_A/display format = comma12.; define Col_B/ display format = percentn12.1; define Col_C/display format = percentn12.1 ; define Col_D/format = comma12. ; define Col_E/display format = comma12.; define Col_F/display format = comma12.; define Col_X/display format = comma12.; define Col_Y/display format = dollar12.2; define Col_Z/display format = dollar12.; compute dimension1; if dimension1 = 'Total' then call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1]'); endcomp; compute Col_A; call define(_COL_,'style','style=[borderleftcolor=black borderleftwidth=1]');endcomp; compute Col_X; call define(_COL_,'style','style=[borderleftcolor=black borderleftwidth=1]');endcomp; run; ods pdf close; Thank you 🙂 Below is the my expected format: Total = Total_Split1 and Total_Split2 Total=Total_SplitA+Total_SplitB+Total_SplitC+Total_SplitD (split in a different way)

new_sas_user_4 · ‎05-31-2019

Thank you 🙂

new_sas_user_4 · ‎05-31-2019

This might have been a simple thing for you ...but you saved me so much more reading.. I am doing this for the first time and had been reading stuff from ..SMTP setup, a few other pdfs... etc to resolve this... Thanks a tonnnnnnnnnnn 🙂 Any reading material on proc report? Any material on formatting a ods pdf ?

new_sas_user_4 · ‎05-31-2019

I am getting a similar error.... I kindoff understand your response but how can rectify the problem of "Physical file does not exist".. My code is : %let report_file = %sysfunc(pathname(work)); ods pdf file="&report_file/Name_of_file.pdf" pdftoc=1 startpage=yes bookmarklist=hide; .......proc report portion............. ods pdf close; filename mailbox email attach='&report_file/Name_of_file.pdf' to ='my_email@sas.com' from='my_email@sas.com' replyto='my_email@sas.com' subject='mail file' ;run; data _null_; file mailbox; put "Hi,"; Put "Attached is the Report"; put ; put "Regards"; run; I get the error: ERROR: Error opening attachment file:&report_file/Name_of_file.pdf. ERROR: Physcial file does not exist, /sasnoapps/prodnoconfig/Lev1/Server1/&report_file/Name_of_File.pdf. Please help!

Online Status	Offline
Date Last Visited	‎05-09-2025 10:06 AM

Re: Sort and label data in Groups

Re: Sort and label data in Groups

Sort and label data in Groups

Re: proc contents in teradata

proc contents in teradata

Re: Teradata Passthrough Help

Teradata Passthrough Help

Re: Managing the layout of tabs in SAS EG

Managing the layout of tabs in SAS EG

Re: Creating a Data Model based on prior month

Re: Grouping?

Re: Grouping?

Sum and Case Statement Help

Re: sending excel file to outlook email through sas

Re: sending excel file to outlook email through sas

Managing the layout of tabs in SAS EG

Re: Unable to load image- ods pdf

Re: Changing Data Output from Columns to Rows

Teradata Dates Modification

Re: Number series for past 12 months

Re: Number series for past 12 months

Number series for past 12 months

Re: Freq count from another dataset

Re: Freq count from another dataset

Re: Freq count from another dataset

Re: Freq count from another dataset

Freq count from another dataset

Re: proc report pdf help!

Re: proc report pdf help!

proc report pdf help!

Re: ERROR: Error opening attachment file

Re: ERROR: Error opening attachment file

Re: ERROR: Error opening attachment file