SAS Support Communities

AmitRathore · ‎07-13-2015

Hi, Please try this. Hope this will help you. data try; infile datalines; input name $ content $; datalines; RE_OP Sydney RE_OP Brussels RE_OP Paris RE_TRF Newyork RE_TRF Washington RE_TRF Houston RE_TRF Dayton ; run; proc sql noprint; select distinct name into : x separated by ' ' from try; quit; %macro create; %do i = 1 %to %eval(%sysfunc(count(&x, %str( )))+1); data %scan(&x,&i.); set try; if name = "%scan(&x,&i.)"; run; %end; %mend; %create Br,Amit

AmitRathore · ‎07-03-2015

Hi Vishan, If I understood you correctly, you cannot change a specific value from numeric to character. If need one has to apply the format to the variable and it applies to all values within that variable. So if you have numeric column(right aligned) you can convert the same to character(left aligned) by PUT function. data test; x = 111; output; x = 222; output; run; data test1; set test; y = put(x, 3.); put y =; run; Hope this suffices your requirement. Br,Amit

AmitRathore · ‎07-03-2015

Hi Ved, As I explained in my previous post, local macro variable is not created until a request is made. If any local symbol table is already present then all the macros created within the macro call are local. There could be two methods to trigger the Local symbol table 1. Using the keyword and/or positional parameters within the macro call 2. Using %LOCAL statement in the macro. If I re-create your program like : %let x=15; %macro a(x = 11); %let x=10; %put x inside macro a= &x; %mend a; %a; %put x outside the macro a = &x; Now the value of 'x' will be 15. Because defining the Keyword parameter(x = 11) triggers the local symbol table and now all the macro created within the macro 'a' will be local in scope. This will not effect the value of 'x' outside the macro 'a'. Hope I answered your query. Br,Amit

AmitRathore · ‎07-02-2015

Hey FastEddie, I do not why you want to extract the excel output but to do Excel Tagsets is very good options. Please check if below program helps you : ods listing close; footnote; title ' '; ods tagsets.excelxp file="C:\temp\test.xls" style=sasweb options(sheet_interval='none' embedded_titles='yes' sheet_name='test' default_column_width="20, 20, 20, 30" width_fudge='0.75' skip_space='0,0,0,0,1' autofilter = 'all' frozen_headers="1" ); proc report data=work.variance out = newdata; column Cust_ID iDATE Variance; define Cust_ID / group; define iDATE / across; define Variance / sum; run; ods tagsets.excelxp close; ods listing; quit; Br, Amit

AmitRathore · ‎07-02-2015

Dear Akordula, Please check the below code and see if it works for you. data mydata; x = 1; output; x = 2; output; x = 3; output; x = 3; output; run; %macro sendmail; proc sql noprint; select count(*) into :n from mydata where x = 3; quit; %put &n; %if &n gt 0 %then %do; %put your email code here; %end; %else %do; %put Value are no values to print/mail; %end; %mend; %sendmail

AmitRathore · ‎07-02-2015

Hi Ved, A local symbol table is not created until a request is made to create a local variable. Macros that do not create local variables do not have a local table. In you case no local variable is created hence the value of x is updated from 15 to 10 in Global Symbol Table. However, if you add %local x; in your macro then the program will create two symbol table one : Global Symbol Table having x = 15 and Local Symbol Table having x = 10. And outside the macro the value x will resolve to 15 because after the completion of execution of Macro local symbol table will be deleted. Below table will give you better picture : Does macvar exist in the local symbol table? Yes Retrieve the value of macvar from the local symbol table. No Does macvar exist in the global symbol table? Yes Retrieve the value of macvar from the global symbol table. No Return the tokens to the word scanner. Issue a warning message to the SAS log to indicate that the reference was not resolved.

AmitRathore · ‎07-02-2015

The reason of comparing program is that I need to migrate some SAS programs from SDD3.5(windows) to SDD4.5(unix). And due to this I have to make same changes in the program like changes backward slash ('\') to forward slash ('/'). After making changes I need to make sure that anything else is not changed accidently. Br,Amit

AmitRathore · ‎07-02-2015

Dear Experts, Is there any way to compare two SAS programs programatically. One way I know is to compare through TextPad but that is more of manually. Br, Amit

AmitRathore · ‎06-30-2015

Hi Kurt, Thanks for suggesting this options. It solves many of my problems also. Hey LineMoon, below is the code for your reference : options ERRORABEND; data t1; x = 1; y = 'a'; output; x = 2; y = 'b'; output; x = 3; y = 'c'; output; run; data t2; x = 4; z = 'a'; output x = 5; z = 'b'; output; x = 6; z = 'c'; output; run; proc sql noprint; create table t3 as select * from t1 outer union corr select * from t2; quit;

AmitRathore · ‎06-29-2015

Hi Experts, Using an index in table momstatefips1 is one of options to reduce the processing time. An index can reduce the time required to locate a set of rows, especially for a large data file. proc sql noprint; create index x on work.momstatefips1(x); create table ds as select a.* from a inner join b on a.momstatefips1 = b.momstatefips; quit; Br, Amit

AmitRathore · ‎06-26-2015

Mistake. Updated the correct program. I picked the dataset from work. Thanks, Amit

AmitRathore · ‎06-26-2015

Hi Kurt, Very impressive program. I am pasting a simple version of your program. I hope this will also work. data data2; infile datalines; input org$ orgid$; datalines; a1 a001 a2 a002 a3 a003 a4 a004 a5 a005 b1 b001 b2 b002 b3 b003 b4 b004 b5 b005 ; run; data data2fmt; keep start label fmtname; retain fmtname '$orgfmt'; set data2(rename = (org = start orgid = label)); run; proc format library = work cntlin = work.data2fmt; run; data data3; infile 'C:\temp\org.txt'; input org1 $ org2 $ org3 $ org4 $ org5 $; format org1-org5 $orgfmt.; run;

AmitRathore · ‎06-26-2015

Hi Robert, I tried to reconstruct your program. See if below will help you. data have; input CUSTOMER_ID date anydtdte23.; format date date9.; cards; 1057086 21AUG2014 1057086 25AUG2014 1057086 17SEP2014 1057086 17SEP2014 1057086 19SEP2014 1057086 26SEP2014 ; proc sort; by customer_id date; run; data nhave; set have; by customer_id date; if first.customer_id then output; run; data wantz; if _N_ = 1 then set nhave(rename=(date = date1)); set have; days_between = dif(date); drop date; rename date1 = date; run; proc transpose data = wantz out = nwant(drop = _name_ Purchase_1) prefix = Purchase_; by customer_id date; var days_between; run; Br,Amit

AmitRathore · ‎06-25-2015

Small input from my side also. Just use 'TRIM function' with _infile_ and your if condition will match *Program A; data a; input; if trim(_infile_)=: "abc" then b=1; output; put _infile_ b=; datalines; ab ; run; *Program B; data a; infile "C:\temp\file.txt"; input; if trim(_infile_)=:"abc" then b=1; output; put _infile_ '*****' b=; run;

AmitRathore · ‎06-25-2015

Hi Tarun, Please see below code if it works for you: filename nwfile 'C:\temp\sales.txt'; data sales; length email_id $ 25; infile nwfile ; input emp_code $ name $ dept $ li_revenue $ mf_revenue $ gi_revenue $ total_revenue $ email_id $; run; proc transpose data = sales out = newsales name=variable prefix = value; by emp_code name email_id; var li_revenue mf_revenue gi_revenue total_revenue; run; data newsales1; set newsales; call symput(trim(variable)||trim(emp_code), value1); call symput("_"||trim(emp_code), email_id); run; %put _user_; %macro MAILME( _to = ,_cc = ,_subject = ,_attach = ,_text1 = ,_text2 = ,_text3 = ,_text4 = ); options emailsys=smtp emailhost=smtp.ferring.com emailport=25; filename maily email (&_to) To = (&_to) CC = (&_cc) subject = "&_subject" %if &_attach ne NO %then %do; attach = "&_attach" %end;; data _null_; file maily; put "THIS IS AN AUTO GENERATED E-MAIL"; put "&_text1"; put "&_text2"; put "&_text3"; put "&_text4"; put " "; put "REGARDS"; put "XYZ Bank"; put "Corporate Office"; run; %mend; %macro chk; %do i = 1 %to 1; %MAILME(_to = "&&_&i." ,_cc = "&&_&i." ,_subject = Issue Revenue ,_attach = NO ,_text1 = Li Revenue -- &&li_revenue&i./- ,_text2 = MF Revenue -- &&mf_revenue&i./- ,_text3 = GI Revenue -- &&gi_revenue&i./- ,_text4 = Total Revenue -- &&total_revenue&i./- ); %end; %mend; %chk

Online Status	Offline
Date Last Visited	‎10-25-2018 12:51 AM

SAS Support Communities

Re: Create multiple datasets from single dataset

Re: How to Change Format of Column in SAS?

Re: what is the logic of this macro?

Re: Column labels not exporting to Excel

Re: Send Email Only When Data Exists

Re: what is the logic of this macro?

Re: Comparing two SAS Programs

Comparing two SAS Programs

Re: stopping the sas execution after the first error or warning

Re: Question about Proc sql in merging two big dataset

Re: how to count id number

Re: what is the logic of this macro?

Re: Need to translate 0-9 to # and A-Z to $

Re: Comparing two SAS Programs

Re: Can we insert observations from multiple sources into a sas datase...

Re: User defined informats

Re: what is the logic of this macro?

Re: what is the logic of this macro?

Re: User defined informats

Re: Create multiple datasets from single dataset

Re: How to Change Format of Column in SAS?

Re: what is the logic of this macro?

Re: Column labels not exporting to Excel

Re: Send Email Only When Data Exists

Re: what is the logic of this macro?

Re: Comparing two SAS Programs

Comparing two SAS Programs

Re: stopping the sas execution after the first error or warning

Re: Question about Proc sql in merging two big dataset

Re: The difference between two dates by row and then transpose

Re: How to replace values for a few variables?

Re: The difference between two dates by row and then transpose

Re: SAS code problem (simple one)

Re: Want to create detail Report

Follow Us

What is...