About caveman529

caveman529 · ‎07-23-2014

Will pre-sorting actually help in the SQL? It seems to me that proc sql is a different econsystem that don't use the sorting in the data to make runtime shorter. Instead, it just does its own thing. That is why I'm thinking use DATA STEP to force SAS to use the sorting I've done. Sorting the data takes only about 30 seconds. Inner join is necessary here so that I can track how many missing variables or the quality of the matching. If I use inner join, I won't be able to know if there are duplicate merge and etc. In fact, I tried to use group by and having statement to make the matching even more robust but when I run that code, I found the disk activity is several KB per second, which is abnormally low given the system resource that SAS can deploy. Does changing some of the system option help?

caveman529 · ‎07-23-2014

Hi, Ksharp: The data is not too big. Only 6 GB in total and much smaller if I keep only a few variables. But it take extremely long time to run on a PC with plenty of system resources. Hardware is definitely not a constraint. It is possible that the algorithm deployed by SQL is not efficient. I used _method buffersize=1G to no avail.

caveman529 · ‎07-23-2014

Thank you, RW9! The abs() function is there to allow for difference in date within 1 day because sometimes the database has date difference. And 1 day difference is okay.

caveman529 · ‎07-23-2014

Dear All: Could you tell me what is the DATA STEP equivalent for the following SQL statement? When I run the following code, SAS runs extremely slow on a high-end workstation. So I think the sort sort merge approach may help in this situation. Thank you ~ proc sql; create table want as select a.*, coalesce(a.condition, b.condition) as condition from have1 as a left join have2 as b on a.patient_id = b.patient_id and 0 <= abs(a.visit_date - b.visit_date) <= 1 and a.type = b.type; quit;

caveman529 · ‎07-09-2014

Hi, Cynthia: Thank you so much! Yes, I just want to send my manually-coded SAS code to the Excel. Your code works great!

caveman529 · ‎07-07-2014

Thank you so much, Cynthia! This is magic! I'm being a little greedy here: I usually send my resulting SAS dataset to Excel at the end of my SAS code within the SAS Enterprise Guide. It it possible to "send" the SAS code to the Excel "code" worksheet? Thank you!

caveman529 · ‎07-07-2014

Could you tell me how to implement what RW9 suggested? "For the second part, perhaps putting comments on a separate page, then this is possible. You can use either proc export multiple times to write multiple sheets, or my suggestion would be to use ods tagset.excelxp (search for this, there are many topics on it). Put your data in one dataset, comments in another, then proc report each of them with ods statements to get a multi-tab spreadsheet with data on one sheet and comments on another."

caveman529 · ‎07-07-2014

Hi, everyone. Thanks for the help here. I hope to add another worksheet within the Excel file that I can type in whatever notes I want to attach to the Excel file so that I know which Excel file corresponds to which SAS code. It is sort of similar to adding a label but I think SAS dataset label has character length limitation I remember. Thanks!

caveman529 · ‎07-07-2014

How to add comments notes (maybe to a different tab) for the SAS data I'm about to export to the Excel file? I find myself frequently confused about which code is used for generating which Excel file. Thank you!

caveman529 · ‎06-16-2014

Thank you, KPS! I'll try it out and report back.

caveman529 · ‎06-12-2014

I hope to use statistical procedure to partition a variable of a panel dataset into cross-sectional variation and time-series variation (within and between variation). Suppose my data looks like: Y X ID DATE I hope to know how Y varies cross-sectionally according to ID holding DATE fixed. I also hope to know how Y varies according to DATE time-series, holding ID fixed. My description may not conform to statistical lingo, could you offer me some advice on how to do so? Thank you –

caveman529 · ‎06-12-2014

I hope to use statistical procedure to partition a variable of a panel dataset into cross-sectional variation and time-series variation. Suppose my data looks like: Y X ID DATE I hope to know how Y varies cross-sectionally according to ID holding DATE fixed. I also hope to know how Y varies according to DATE time-series, holding ID fixed. My description may not conform to statistical lingo, could you offer me some advice on how to do so? Thank you –

caveman529 · ‎04-03-2014

Yes. formatting is exactly the same.

caveman529 · ‎04-03-2014

Never coded myself SAS macro before. I have a list of text files in a Linux directory with the name convention like below: have_20090414.output.txt I hope to extract the date first from all the text files within in that directory say /path/to/file/ and then feed the date just created into the dt macro variable below and repeat the process as below for all the files in that directory. Could you please tell me how to best code the macro? %let dt = 20111004; DATA want.want_&dt; LENGTH date 8 key $ 15 INFILE "d:\have&dt..output.txt" LRECL=32767 ENCODING="WLATIN1" DLM='7c'x MISSOVER DSD ; INPUT RUN; proc sql; create table want.xwant_&dt as select distinct date, key from want.want_&dt; quit; I know that if the files in the directory are all SAS then it might be easier... data _null_; set abccont end=last; by memname; i+1; call symputx('name'||trim(left(put(i,8.))),memname); if last then call symputx('count',i); run; %macro transform; %do i=1 %to &count; ???; %end; %mend transform; %end; %mend combdsets;

caveman529 · ‎04-02-2014

I know the SAS has this nice feature of reading text file directly from gzip files. Is it possible to gzip the sas7bdat file first and the ask the SAS to read from that gzipped SAS file instead? That would save me a lot of HDD space Thanks -

Online Status	Offline
Date Last Visited	‎03-31-2020 01:11 PM

Re: Filling gap in a panel data

Filling gap in a panel data

Re: hash equivalent of sql

Re: execute same procedure over multiple datasets

Re: SAS way for calculating YoY change

Re: execute same procedure over multiple datasets

Re: SAS way for calculating YoY change

SAS way for calculating YoY change

Re: execute same procedure over multiple datasets

execute same procedure over multiple datasets

Re: data step to merge for a date range

Re: data step to merge for a date range

Re: data step to merge for a date range

data step to merge for a date range

Re: How to add note to Excel when export SAS data?

Re: How to add note to Excel when export SAS data?

Re: How to add note to Excel when export SAS data?

Re: How to add note to Excel when export SAS data?

How to add note to Excel when export SAS data?

Re: Panel Data Variance Decomposition

Panel Data Variance Decomposition

Panel Data Variance Decomposition

Re: How to list all text file in a directory and then feed into macro?

How to list all text file in a directory and then feed into macro?

Can I read gzipped SAS dataset on the fly?