About buckeyefisher

Kurt_Bremser · ‎02-04-2023

You probably want this: data want; set have; by case_number; retain repeat1; if first.case_number then repeat1 = ""; if party_role ne "" then repeat1 = party_role; else party_role = repeat1; drop repeat1; run;

Kurt_Bremser · ‎03-06-2021

Write your code in a manner that makes data step code vs. macro code visible: data whole; set %do j=1 %to &n; datafile&j %end; ; run;

Patrick · ‎10-17-2019

Here another coding option which writes the executed statements in a nice way into the SAS log. %macro upcaseVarnames(lrefTbl); %local lref tbl; %let lref=%upcase(%scan(work.&lrefTbl,-2,.)); %let tbl=%upcase(%scan(&lrefTbl,-1,.)); filename codegen temp; data _null_; file codegen; /* file print;*/ length _cmd $65; set sashelp.vcolumn(where=(libname="&lref" and memname="&tbl")) end=_last; if _n_=1 then do; put "proc datasets lib=&lref nolist;" / " modify &tbl;" / " rename" ; end; _cmd=cats(name,'=',upcase(name)); put " " _cmd; if _last then do; put " ;" / " run;" / " contents data=&tbl;" / " run;" / "quit;" ; end; run; %include codegen / source2; filename codegen clear; %mend; data sample sample2; set sashelp.class; run; %upcaseVarnames(sample) %upcaseVarnames(sample2)

Ksharp · ‎06-06-2019

data work.seta; input Case_number : $14. subclass ; datalines; 5-10-cv-00123 434 5-10-cv-00123 451 5-10-cv-00123 478 2-10-cv-00599 510 2-10-cv-00599 512 ; data work.setb; input Case_number : $14. subclass ; datalines; 5-10-cv-00123 221 5-10-cv-00123 434 2-10-cv-00599 510 2-10-cv-00599 512 ; proc sql; create table temp_a as select case_number,count(*) as n_from_a from seta group by case_number; create table temp as select case_number,count(*) as match_in_b from ( select * from seta intersect select * from setb ) group by case_number; create table want as select a.*,match_in_b ,(n_from_a=match_in_b) as flag from temp_a as a left join temp as b on a.case_number=b.case_number; quit;

shekharkaul2007 · ‎05-29-2017

Hi, My queries are: 1) I have 3 files as "Excel 1" "Excel 2" "Excel 3" and after the import I have all the files but by the name "Excel" "Excel" "Excel". I cant sort it out. 2) I am new to this type of import. Can you tell me what does 'dir "E:\Analytics\Excel\*.xls" /b' b means in this link. And how to alter this as per our requirement.

buckeyefisher · ‎05-22-2017

This also looks intersting. Thanks !!

buckeyefisher · ‎09-06-2016

Shumel, Although I am yet to verify all possible combinations, initial results are promising. Thanks,

buckeyefisher · ‎11-25-2015

Steelers_In_DC - I want to count unique countries. Hence, in 2002 KSA is counted once.

LinusH · ‎10-10-2015

Does not the original column name or order have any meaning? How will you use the table further on? I would sugest that you transpose the table instead, resulting in record, class_no and class_value columns. See PROC TRANSPOSE for details.

PGStats · ‎10-08-2015

Unfortunately, the SQL UPDATE syntax is quite rigid. You must repeat the subquery on each assignment proc sql; update want as a set CntA = (select coalesce(sum(A),0) from have where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1) , CntB = (select coalesce(sum(B),0) from have where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1) , CntC = (select coalesce(sum(C),0) from have where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1) , CntD = (select coalesce(sum(D),0) from have where (a.Originator = Originator) and year between (a.year)-8 and (a.failureyear)-1); quit; (untested)

Reeza · ‎10-05-2015

It's probably faster to try rather than ask 😉 Also, if all your files start with datafile AND you don't have any other files that start with datafile you can just use: data whole; format field $950.; length field $950.; set datafile:; run;

buckeyefisher · ‎10-05-2015

My code was write just that input date had some issues. Now it works. But I agree with Tom that SQL may not be the best way to modify. For this simple updation, my program takes around 20 to 25 minutes. There are many more calculations lined up and I can not imagine spending a day to get the results. Let me try MODIFY. Thanks all.

Ron_MacroMaven · ‎06-16-2015

Here is code for a fix of the imported data set. %macro loop(dim=3 ,text=%nrstr(a&i) ,testing = 0) /des= 'macro loop: resolve text within loop'; %do i = 1 %to &dim; %unquote(&text) %end; %mend; options mprint; %put %loop(); %put %loop(text=%nrstr(_b&i)); %put %loop(text=%nrstr(c&i = _c&i)); DATA mydata; b1 = '1'; b2 = '2.2'; b3 = '0.03'; output; stop; run; DATA now_numeric; array c_(*) $32 %loop(text=%nrstr(_b&i)); array n_(*) %loop(text=%nrstr(b&i)); drop _:;*temp variables with prefix of underline; set mydata(rename = (%loop(text=%nrstr(b&i = _b&i)))); do _i = 1 to 3; n_(_i) = input(c_(_i),best.); end; PROC sql; describe table &syslast; quit;

buckeyefisher · ‎05-28-2015

this worked. We dont need to convert this to numeric. format dateinsas year.; dateinsas=mdy(1,1,dateinchar); Thanks!!!

Ron_MacroMaven · ‎04-27-2015

There are just a few reasons to use macros * conditionals: %if * loops: %do * %sysfunc, %sysevalf * write a function to return a value. In the example you have posted, I see none of the above. Others have pointed out the %include statement, which is what I, too, recommend. The differences, in your situation, where the code is only repetition and has not need of macro statements * code in the %included program is always compiled in that step. * a macro call generates a search of the sasautos library (set of folders) and an %include of the file containing the macro the code is compiled before being used in the step. From the way I read your post, you are using this set of prx* statements in multiple programs, but not more than once in any one program. If that is true then there is no benefit to using a macro for this suite of statements. Since I run all my programs in batch, you may reply that you run several different programs in a session, in which case the savings gained by the macro being compiled are worthy of consideration. Caveat: just do not change the macro and expect the changes to show up in your next call of the macro. you are using the compiled version of the macro you have to recompile the macro, for which there are two ways to get it updated: 1. options mrecall; 2. %include 'my-macro'; Ron Fehd macro maven

Online Status	Offline
Date Last Visited	‎02-10-2023 03:15 AM

Filling missing values with group and subgroup

Re: Importing multiple Excel files

Importing multiple Excel files

Re: Changing variables from upper to lower case

Re: Changing variables from upper to lower case

Changing variables from upper to lower case

Re: Looping using 2 files

Looping using 2 files

Re: How to extract specific 4 digits from large string where the locat...

How to extract specific 4 digits from large string where the location ...

Re: Importing multiple Excel files

Re: Importing multiple Excel files

Re: Importing multiple Excel files

Re: Looping using 2 files

Re: data truncation while importing and merging excel sheet

Re: Filling missing values with group and subgroup

Re: Importing multiple Excel files

Re: Changing variables from upper to lower case

Re: Looping using 2 files

Re: Importing multi Excel files

Re: How to extract specific 4 digits from large string where the locat...

Re: Counting Alliance partner unique diadic relationships

Re: SAS Proc means

Re: Collapsing multiple columns into few

Re: multiple SET in PROC SQL

Re: data truncation while importing and merging excel sheet

Re: PROC SQL sum where

Re: Convert columns defined as character to numeric for more than 1600...

Re: character to date

Re: Fequently used code in a macro