About tekish

tekish · ‎11-21-2017

Tom, When I run the .exe I have an excel output. thanks,

tekish · ‎11-21-2017

/****************************************************/ /* SAVE EXCEL FILES FROM DOWNLOAD */ /***************************************************/ %LET OUT=C:\DOWNLOAD\; %LET REPORT=BOOK1; FILENAME COMMANDS DDE 'EXCEL|SYSTEM'; RUN; %LET FILE = &OUT.&REPORT..XLSX; RUN; DATA _NULL_; FILE COMMANDS; PUT "[SAVE.AS(""&FILE"")]"; PUT '[QUIT]'; RUN;

tekish · ‎11-20-2017

Reeza, The reason I want to create a macro column is I have 200 column names to be labeled.

tekish · ‎11-20-2017

I have report with more than 200 columns and I want to create a macro column names. here is the sample codes: data class; set sashelp.class; nodeid100="";nodeid101="";nodeid102="";nodeid103="";nodeid104="";nodeid105="";nodeid106="";nodeid107=""; nodeid108="";nodeid109="";nodeid110="";nodeid111="";nodeid112="";nodeid113="";nodeid114="";nodeid115=""; run; ods excel file="\test.xlsx"; proc report data=class; column name substr(nodeid136,7); run; ods _all_ close; my expected output would be: Name 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 Alfred Alice Barbara Carol Henry James Jane Janet Jeffrey John Joyce Judy Louise Mary Philip Robert Ronald Thomas William

tekish · ‎11-14-2017

KurtBremser, It is running with out error, but my biggest concern is how do I save the excel output from .exe. I got this message NOTE: The infile 'myfile.exe' is: Unnamed Pipe Access Device, PROCESS=myfile.exe, RECFM=V,LRECL=32727 NOTE: 0 records were read from the infile 'myfile.exe'. NOTE: The data set WORK.DOWNLOAD has 0 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 44.61 seconds cpu time 0.04 seconds

tekish · ‎11-14-2017

Dear experst; How to call .exe and get as SAS dataset. when you run the .exe it gives me excel output and I want to save the excel file and process the file using SAS. data download; infile 'myfile.exe' pipe; length A $50; input A$ B c; * read from output; putlog _infile_; * show it in the log; run;

tekish · ‎11-11-2017

Dear expert; I want to create two undeliverable email template(ale and female). How do I create a macro on the subject field. here is my sample codes. data class; set sashelp.class; length email $50; if sex="M" then flag=1; else flag=0; if name="Alfred" then email="Alfred.com"; else if name="Alice" then email="Alice.com"; else if name="Barbara" then email="Barbara.com"; else if name="Carol" then email="Carol.com"; else if name="Henry" then email="Henry.com"; else if name="James" then email="James.com"; else if name="Jane" then email="Jane.com"; else if name="Janet" then email="Janet.com"; else if name="Janet" then email="Janet.com"; else if name="Janet" then email="Janet.com"; else if name="Jeffrey" then email="Jeffrey.com"; else if name="John" then email="John.com"; else if name="Joyce" then email="Joyce.com"; else if name="Judy" then email="Judy.com"; else if name="Judy" then email="Judy.com"; else if name="Louise" then email="Louise.com"; else if name="Mary" then email="Mary.com"; else if name="Philip" then email="Philip.com"; else if name="Mary" then email="Mary.com"; else if name="Robert" then email="Robert.com"; else if name="Ronald" then email="Ronald.com"; else if name="Thomas" then email="Thomas.com"; else if name="William" then email="William.com"; run; proc sort data=class; by email; run; Data _null_; set class; by email; if flag=1 then call symputx('Male','1'); run; %put male=&Male; 　 %MACRO B_EMAIL(EMAIL); options emailsys=SMTP emailport=XX; FILENAME MYMAIL EMAIL TO = "myemailaddress@t.com" %if &Male=1 %then %do; SUBJECT = "Male Undeliverable Email Address" %end; %else %do; SUBJECT = "FeMale Undeliverable Email Address" %end; CT = "TEXT/HTML" BCC = "myemailaddress@t.com" FROM = "myemailaddress@t.com" SENDER = "myemailaddress@t.com" REPLYTO = "myemailaddress@t.com" DATA TEMP; SET class; BY email; IF email = "&EMAIL"; RUN; DATA _NULL_; FILE MYMAIL; SET TEMP; %IF &TESTING = Y %THEN %DO; EMAILADDRESS = "myemailaddress@t.com"; %END; PUT '!EM_TO! ' EMAILADDRESS; %if &male=1 %then %do; PUT "Dear Mr." Name; %end; %else %do; PUT "Dear Mrs." Name; %end; PUT "<p>"; PUT "You are receiving this email because the following respondent’s Welcome email was returned to us as undeliverable:"; PUT "<p>"; PUT EMAIL; PUT "<p>"; PUT "Please correct the respondent’s email address and re-send the email"; PUT '</font></body></html>'; RUN; FILENAME MYMAIL CLEAR; %MEND B_EMAIL; %LET TESTING = Y; DATA _NULL_; SET class; BY email; CALL EXECUTE('%B_EMAIL('||email||')'); RUN;

tekish · ‎10-27-2017

Reeza, Thanks.

tekish · ‎10-27-2017

Dear Experts, I have a large dataset with so many records, I want to fill the missing values. I to create new two variables, "Principal" and "Vice". data school; length schoolname $20 teachername $50; input schoolname 1-21 subject$ 23 ID 26-27 teachername 32-54 Qtype; cards; holmes middle school M -1 Doris Wilson 5 holmes middle school M 0 Amy Smith 7 holmes middle school R 1 Mrs. A. T. Whitecotton 6 holmes middle school M 2 Geraldine Carpenter 6 holmes middle school R 3 Jennie Crigler 6 holmes middle school M 4 Lida Gray Gibbs 6 holmes middle school M 5 Irene Ball 6 holmes middle school R 6 Wynona James 6 holmes middle school M 7 Moneaka Elam 8 holmes middle school M 8 Doris Stephens 8 holmes middle school G 9 Catherine Clark 8 holmes middle school M 10 Elma Kemp 8 holmes middle school M 11 Lois Pagett 8 holmes middle school M 12 Ruby Partin 8 holmes middle school H 13 Fances Nugent 8 ; run; proc sort data=school out=school; by schoolname ID Qtype; run; 　 data school1; set school end=eof; by schoolname ID Qtype; if ID=-1 and Qtype=5 then principal=TeacherName; if ID=0 and Qtype=7 then Vice=TeacherName; run; the expected output. schoolname teachername subject ID Qtype principal Vice holmes middle school Doris Wilson M -1 5 Doris Wilson Amy Smith holmes middle school Amy Smith M 0 7 Doris Wilson Amy Smith holmes middle school Mrs. A. T. Whitecotton R 1 6 Doris Wilson Amy Smith holmes middle school Kemp Elma M 1 8 Doris Wilson Amy Smith holmes middle school Pagett Lois M 1 8 Doris Wilson Amy Smith holmes middle school Partin Ruby M 1 8 Doris Wilson Amy Smith holmes middle school Nugent Fances H 1 8 Doris Wilson Amy Smith holmes middle school Geraldine Carpenter M 2 6 Doris Wilson Amy Smith holmes middle school Crigler Jennie R 3 6 Doris Wilson Amy Smith holmes middle school Lida Gray Gibbs M 4 6 Doris Wilson Amy Smith holmes middle school Ball Irene M 5 6 Doris Wilson Amy Smith holmes middle school James Wynona R 6 6 Doris Wilson Amy Smith holmes middle school Elam Moneaka M 7 8 Doris Wilson Amy Smith holmes middle school Stephens Doris M 8 8 Doris Wilson Amy Smith holmes middle school Clark Catherine G 9 8 Doris Wilson Amy Smith river middle school Ione Ransdell M -1 5 Ione Ransdell AElgelina Atterbury river middle school AElgelina Atterbury M 0 7 Ione Ransdell AElgelina Atterbury river middle school MWynona James R 1 6 Ione Ransdell AElgelina Atterbury river middle school Hopson Nannie M 1 8 Ione Ransdell AElgelina Atterbury river middle school Lutie Caldwell M 1 8 Ione Ransdell AElgelina Atterbury river middle school Maydee Mallory M 1 8 Ione Ransdell AElgelina Atterbury river middle school Lois Pagett H 1 8 Ione Ransdell AElgelina Atterbury river middle school Abigail Marlow M 2 6 Ione Ransdell AElgelina Atterbury river middle school Jennie Crigler R 3 6 Ione Ransdell AElgelina Atterbury river middle school Lida Gray Gibbs M 4 6 Ione Ransdell AElgelina Atterbury river middle school Ball Irene M 5 6 Ione Ransdell AElgelina Atterbury river middle school James Wynona R 6 6 Ione Ransdell AElgelina Atterbury river middle school Elam Moneaka M 7 8 Ione Ransdell AElgelina Atterbury river middle school Carpenter Imogene M 8 8 Ione Ransdell AElgelina Atterbury river middle school Flowers Martha G 9 8 Ione Ransdell AElgelina Atterbury

tekish · ‎10-23-2017

Reeza, It works when I added all the codes. Thank you for you help. here is the final code. data want; set sashelp.class; rownum=_n_; if mod(_n_, 6) = 1 then group+1; newvalue=mod(rownum,6); if newvalue=0 then newvalue1=6; else newvalue1=newvalue; 　 run; data want1(drop=newvalue newvalue1); set want; if newvalue1=1 then neworder=1; if newvalue1=2 then neworder=4; if newvalue1=3 then neworder=2; if newvalue1=4 then neworder=5; if newvalue1=5 then neworder=3; if newvalue1=6 then neworder=6; run; proc sort data=want1 out=test; by group neworder; run; The expected output. Name Sex Age Height Weight rownum group neworder Alfred M 14 69.0 112.5 1 1 1 Barbara F 13 65.3 98.0 3 1 2 Henry M 14 63.5 102.5 5 1 3 Alice F 13 56.5 84.0 2 1 4 Carol F 14 62.8 102.5 4 1 5 James M 12 57.3 83.0 6 1 6 Jane F 12 59.8 84.5 7 2 1 Jeffrey M 13 62.5 84.0 9 2 2 Joyce F 11 51.3 50.5 11 2 3 Janet F 15 62.5 112.5 8 2 4 John M 12 59.0 99.5 10 2 5 Judy F 14 64.3 90.0 12 2 6 Louise F 12 56.3 77.0 13 3 1 Philip M 16 72.0 150.0 15 3 2 Ronald M 15 67.0 133.0 17 3 3 Mary F 15 66.5 112.0 14 3 4 Robert M 12 64.8 128.0 16 3 5 Thomas M 11 57.5 85.0 18 3 6 William M 15 66.5 112.0 19 4 1

tekish · ‎10-20-2017

Reeza, This is the code data want; set sashelp.class; rownum=_n_; if mod(_n_, 6) = 1 then group+1; run; this is the output: Name Sex Age Height Weight rownum group Alfred M 14 69.0 112.5 1 1 Alice F 13 56.5 84.0 2 1 Barbara F 13 65.3 98.0 3 1 Carol F 14 62.8 102.5 4 1 Henry M 14 63.5 102.5 5 1 James M 12 57.3 83.0 6 1 Jane F 12 59.8 84.5 7 2 Janet F 15 62.5 112.5 8 2 Jeffrey M 13 62.5 84.0 9 2 John M 12 59.0 99.5 10 2 Joyce F 11 51.3 50.5 11 2 Judy F 14 64.3 90.0 12 2 Louise F 12 56.3 77.0 13 3 Mary F 15 66.5 112.0 14 3 Philip M 16 72.0 150.0 15 3 Robert M 12 64.8 128.0 16 3 Ronald M 15 67.0 133.0 17 3 Thomas M 11 57.5 85.0 18 3 William M 15 66.5 112.0 19 4

tekish · ‎10-20-2017

Name Sex Age Height Weight rownum newvalue newvalue1 neworder Alfred M 14 69.0 112.5 1 1 1 1 Barbara F 13 65.3 98.0 3 3 3 2 Henry M 14 63.5 102.5 5 5 5 3 Alice F 13 56.5 84.0 2 2 2 4 Carol F 14 62.8 102.5 4 4 4 5 James M 12 57.3 83.0 6 0 6 6 Jane F 12 59.8 84.5 7 1 1 7 Jeffrey M 13 62.5 84.0 9 3 3 8 Joyce F 11 51.3 50.5 11 5 5 9 Janet F 15 62.5 112.5 8 2 2 10 John M 12 59.0 99.5 10 4 4 11 Judy F 14 64.3 90.0 12 0 6 12 Louise F 12 56.3 77.0 13 1 1 13 Philip M 16 72.0 150.0 15 3 3 14 Ronald M 15 67.0 133.0 17 5 5 15 Mary F 15 66.5 112.0 14 2 2 16 Robert M 12 64.8 128.0 16 4 4 17 Thomas M 11 57.5 85.0 18 0 6 18 William M 15 66.5 112.0 19 1 1 19 Is there any way I can re-order like the above output.

tekish · ‎10-20-2017

Name Sex Age Height Weight rownum group Alfred M 14 69.0 112.5 1 1 Alice F 13 56.5 84.0 2 1 Barbara F 13 65.3 98.0 3 1 Carol F 14 62.8 102.5 4 1 Henry M 14 63.5 102.5 5 1 James M 12 57.3 83.0 6 1 Jane F 12 59.8 84.5 7 2 Janet F 15 62.5 112.5 8 2 Jeffrey M 13 62.5 84.0 9 2 John M 12 59.0 99.5 10 2 Joyce F 11 51.3 50.5 11 2 Judy F 14 64.3 90.0 12 2 Louise F 12 56.3 77.0 13 3 Mary F 15 66.5 112.0 14 3 Philip M 16 72.0 150.0 15 3 Robert M 12 64.8 128.0 16 3 Ronald M 15 67.0 133.0 17 3 Thomas M 11 57.5 85.0 18 3 William M 15 66.5 112.0 19 4

tekish · ‎10-20-2017

Reeza, this will group the first six observation group 1,group 2......group n. this want solve my problem.

tekish · ‎10-20-2017

Reeza, Let's Assume this is one group with 19 observations.

Online Status	Offline
Date Last Visited	‎08-31-2021 04:44 PM

how to calculate week function inside proc report

Re: How to style report in proc report

Re: How to style report in proc report

Re: How to style report in proc report

How to style report in proc report

Re: how to transpose row into columns

how to transpose row into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: how to convert row data into columns

Re: How to style report in proc report

Re: How to add missing columns in existing dataset

Re: How to transpose column

Re: Reorder subrow rownumber

Re: Reorder the rownumber

Re: API connection Using SAS

Re: Reorder subrow rownumber

Re: execute .exe file thru SAS and save the output

Re: execute .exe file thru SAS and save the output

Re: how to create a macro column name in proc report

how to create a macro column name in proc report

Re: execute .exe file thru SAS and save the output

execute .exe file thru SAS and save the output

create a macro for undeliverable email

Re: How to add missing values

How to add missing values

Re: Reorder subrow rownumber

Re: Reorder subrow rownumber

Re: Reorder subrow rownumber

Re: Reorder subrow rownumber

Re: Reorder subrow rownumber

Re: Reorder subrow rownumber