About RW9

RW9 · ‎06-09-2014

Well, its a bit vague but what you will need to do is to get a list from SASHELP.VTABLE with a where clause of your libname. This will give you a list of the datasets in that libname. You can then use that call execute a macro which will sort and merge that onto a base table. E.g %macro add (ds); proc sort data=&ds. out=temp; by idvar; run; data base; merge base temp; by idvar; run; %mend add; data list_of_tables (keep=libname memname); set sashelp.vtable; where libname="WORK"; if _n_=1 then do; call execute('data base; set '||strip(libname)||"."||strip(memname)||'; run;'); /* This takes the first dataset as the base */ end; else call execute('%add('||strip(libname)||"."||strip(memname)||';'); run; Of course this assumes a lot of things - datasets are the same, have the same idvar, there is more than 1 etc. You could build an SQL statement using call execute, this is limited to 256 tables though. As for you coalesce, well, in the merge I would rename the temp variables, and then compare.

RW9 · ‎06-09-2014

What would you like it to output to? In base SAS output destination is set with the ods command, e.g. for rtf: ods rtf file="...."; There are various options for ods (output delivery system), so perhaps read on that.

RW9 · ‎06-09-2014

Hi, Sorry, you don't mention what your Industry data looks like, or what the file is you are reading in. Therefore I will assume the data is as it looks above, and the file is Excel (note I have not tested this): libname myexcel excel "an excel file.xls"; proc sql; create table WANT as select COMPANY.NAME, INDUSTRY.NAME, INDUSTRY.CODE from MYEXCEL.'sheet1'n COMPANY left join INDUSTRY_NAME_TABLE INDUSTRY on index(COMPANY.NAME,INDUSTRY.NAME)>0; /* Note you may want to upcase both, or you may not */ quit;

RW9 · ‎06-09-2014

Hi, What precisely do you want help on as I am not writing all that code. Also, why do you need to convert it to SQL, is it to move it to a database, if so then you need to investigate the database syntax as they have additional functionality which will help. The only thing at a quick glance that I would suggest being difficult is the transpose as SQL is built to work on normalized tables rather than transposed. One final thing. You may be quicker looking at your final datastep and working back from there e.g. data final(drop= /* This becomes proc sql; create table final as set location; /* move to end */ /* These are your select statements */ SEX=input(put(gender_name,$sex.),3.); RACE=input(put(Ethnic_Group,$race.),3.); PAY1=input(put(Financial_Class,$payer.),3.); HOSPID=input(put(Company_Code,$hosp.),4.); DRG=MSDRG_CODE*1; POINTOFORIGINUBO4=put(Admit_Source_Code,$Porign.); MDC=input(put(DRG,MDC.),best.); /* change to case statemtn */ if MSDRG_code="N/C" then MSDRG_code="000"; /* add in here locations for each of the above variables */ /* and join them */ run; Note you can drop all the sort statements as SQL doesn't require pre-sorted data like SAS. Depending on your platform, the formats will only work in SAS. My choice is to remove that and put your formats in another dataset and use that as lookup e.g. (select THIS.CODE_VALUE from CODE_DATASET THIS where THIS.CODE=BASE.CODE) as LONG_VALUE, One final thing, you use arrays at a point in your table, you would need to re-think your logic there as (I mentioned before) SQL is based on normalized (or data going down rather than across) tables. So you may struggle to get the data going across, or have the functionality to process data across. Take it bit by bit also

RW9 · ‎06-06-2014

Hi, Could you clarify what you mean by "Word 2010 is rendering an RTF file created using ODS RTF using Word 97 settings", is this something you are doing in Word? One thing that came up when we moved over to SAS9.3 was that the template language had changed slightly, padding became cellpadding or vice-versa which messed up our outputs. Maybe you could post an example, just simple rtf output and state the steps which is causing your issue.

RW9 · ‎06-06-2014

Or pop single quotes around it: %let csvfile='P&I ARREARAGE_brandonsfile.csv';

RW9 · ‎06-05-2014

Yep, your right, it is 3-8, where on earth did I get the length always = 8 from - previous version of SAS or from other standards?

RW9 · ‎06-05-2014

Sorry, in a hurry there (note the length will still be 8): proc format; value $sex 'Male' = 1 'Female' =2 ; run; data location; attrib gender_name format=$10.; gender_name="Male"; output; gender_name="Male"; output; gender_name="Female"; output; run; data final; attrib sex length=3 format=3.; /* adds the format and length*/ set location; SEX=input(put(gender_name,$sex.),3.); run;

RW9 · ‎06-05-2014

Hi, I am leaving now so no full example, however you want to look at the by statement in a datastep, with a retain or lag on the variables, like: data want; set have; by acct date; retain Day_1_CurBal day_2_curbal_day...; if last.date then output; run;

RW9 · ‎06-05-2014

Numeric variables always have a length of 8. You can apply a format to the variable: data final; attrib sex format=3.; set location; sex=put(gender_name,$sex.); run;

RW9 · ‎06-05-2014

Hi, Change you code to (as you are missing an and: if var1 ne . and var2 ne . and var3 ne . then notmissing=1; else notmissing=0; Also, if you are using SAS 9.2 then you can use the CMISS() function and if > 0 then 1 else 0. There are other ways too, check out arrays if you have lots of variables to check, then you could use a do loop to go over it. data have; attrib var1 var2 var3 notmissing format=best.; var1=2; var2=3; var3=.; output; var1=3; var2=2; var3=4; output; run; data want (drop=i); set have; array var{3}; notmissing=1; /* Assume not missing */ do i=1 to 3; if var{i} = . then notmissing=0; /* if any are missing then set to missing */ end; run; The benefit of the above becomes clear when you have 10 or 20 var variables, as you just need to increase the loop size.

RW9 · ‎06-04-2014

Well, there are some typos in your code for the data _null_ suggestion, here it is updated: data _null_; do i=2011 to 2013; year=put(i, 4.); call execute('proc sql; create table pop_'||year||' as select a.*, b.edu from family a inner join school b on a.ID=b.ID where input(DOS,yymmdd10.) between '||input(year||'-01-01', yymmdd10.)||' and '||input(year||'-12-31', yymmdd10.)||'; quit;'); end; run; Your macro isn't working as the DOS is still character and needs to be input as YYMMDD10., also the other dates need d after them to make them date literals. Please see my other post for the where clause update.

RW9 · ‎06-04-2014

So the where is all wrong then: from: where Date between '&year-01-01' and '&year-12-31'; to (assuming per the other dates that my date is yyyy-mm-dd): where input(Date,yymmdd10.) between "&year-01-01"d and "&year-12-31"d;

RW9 · ‎06-04-2014

Sory to add to your mail Astounding, you would still need to have the letter d after your date literals, e.g. "&year-01-01"d Otherwise the where is trying to compare numeric date to two characters which wont work

RW9 · ‎06-04-2014

Personally I would investigate version control. What you are talking about is keeping a snapshot at certain timepoints. TortoiseSVN for example is free and relatively simple. You would have one copy of the run, and utilizing the version control system that would keep your snapshots. I have worked at a company which had this type of Run 1, dated folders structure and it really is a complete nightmare when working with more than just yourself. The other reason is why keep multiple copies of the same thing, just let a DB backend like SVN keep differences between versions, reduces the size of these things greatly.

Re: How to import excel with guessingrow function

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Re: Deleting rows in Excel with SAS

Re: PROC SGPLOT one command to produce graph for each case? And, quest...

Re: Deleting rows in Excel with SAS

Re: put statement and line break using /

Re: SAS code

Re: How to read from pdf to SAS

Re: Removing last word with length 1 or 2

Re: How to read from pdf to SAS

Re: How to read a macro variable value and convert it in SAS data set

Re: proc format in permanent library

Re: Copy all tables beginning with certain keyword from one lib to oth...

Re: Automated jobs failing because SAS logs contains Warning messages.

Re: Error: BY variables are not properly sorted on data set

Re: Difference between itt and safety populations

Re: ods excel -multiple sheets

Re: Lag function help

Re: ERROR: User does not have appropriate authorization level for file...

Re: Multiple graph in one page

Maxims of Maximally Efficient SAS Programmers

Re: Merge all the tables of Library

Re: proc print in DI Job

Re: Need help with array lookups

Re: SAS TO SQL

Re: ODS RTF & Word 2010

Re: Ampersand in file name, causing problems

Re: Proc Format

Re: Proc Format

Re: filling in missing values with the (only) non-missing value within...

Re: Proc Format

Re: identify not missing observations

Re: can't insert date macro into SQL

Re: can't insert date macro into SQL

Re: can't insert date macro into SQL

Re: Creating subfolders and libnames

SAS Global Forum 2018