TimArm Tracker

Re: How to read observations in multiple records conditionally

TimArm — Thu, 14 May 2015 10:12:29 GMT

In my experience, this kind of problem usually requires stripping out carriage returns using a preprocessing step.

I use the following macro:

%macro replace_crlf(inf,repchar='.');

data _null_;

infile "&inf." recfm=n sharebuffers;

file "&inf." recfm=n;

input a $char1.;

retain open 0;

** This statement toggles the open flag. ;

if a='"' then open=not open;

if a in ('0A'x,'0D'x) and open then put &repchar.;

run;

%mend replace_crlf;

%replace_crlf(filename.csv,repchar=' ');

This will alter the file in place...so if you don't want to risk losing the original, take a copy of it first!

Good luck.

Re: Removing linefeeds

TimArm — Wed, 15 May 2013 15:30:55 GMT

Try using

v1= TRANSLATE(v1,"",'0A'x);

The function TRANWRD looks for white space to delimit words - so probably considers '0A'x as white space.

NOTE: the arguments to TRANSLATE need to be swapped around from TRANWRD.

Tim

Re: Copy/move external file,doesn't work.

TimArm — Wed, 15 May 2013 08:12:51 GMT

Hello. Try this and check the values of rc:

data rc;

rc=system("xcopy \\133.9.206.139\bdp\43430710\Test\a.xls \\133.9.206.139\bdp\43430710\Test\test1");

output;

rc=system("move \\133.9.206.139\bdp\43430710\Test\a.xls \\133.9.206.139\bdp\43430710\Test\test1");

output;

rc=system("copy \\133.9.206.139\bdp\43430710\Test\a.xls \\133.9.206.139\bdp\43430710\Test\test1");

output;

run;

Once you get something that works you can try it again in %sysExec, or keep the above with data _NULL_ instead of data rc (although it can be quite useful to have a work dataset where you can check the return codes).

If you have a drive mapping for \\133.9.206.139\bdp then try using that instead.

Tim

Re: Removing space while resolving macros

TimArm — Thu, 02 May 2013 12:42:30 GMT

Nice one Ksharp. I will use that in future.

Thanks,

Tim

Re: Removing space while resolving macros

TimArm — Thu, 02 May 2013 10:44:22 GMT

Hello santhosh, I think this is a simple solution you could try...

data _null_;

set temperv end=eof;

length t_str $100.;

t_str = cats('CPRNO',put(_n_,10.));

call symput(t_str,CPRNO);

if eof then

t_str = left(put(_n_,20.));

call symput('numrows',t_str);

run;

%put _user_;

Regards,

Tim

Re: Running SAS EG jobs in Windows from Unix

TimArm — Wed, 01 May 2013 14:20:42 GMT

Hello John,

On our unix server we have Samba installed so that we can access the same disk area from unix and Windows. Do you have samba installed - or is it something you can ask your Unix Administrator to install?

This means I can edit a file on Windows as X:\sharename\file.sas, and then include it from a job running on unix as /usr/data/sharename/file.sas

Regards,

Tim

Re: Can someone help me with my formatting?

TimArm — Wed, 01 May 2013 11:07:31 GMT

I think you were almost there. Try this...

Data comb_rate;

Set female_pr male_pr pulserate;
label rate_change='Pulse Rate Change'

BMI='Body Mass Index';

if gender='male' then

do;

BMI=703*weight/(height*height);

end;

if gender='female' then

do;

BMI=695*weight/(height*height);

end;

Rate_change=rate_after-rate_before;

run;

proc print data=comb_rate;

run;

proc format;
value bmifmt

Low-25='Normal'

26-35 ='Low Risk'

36-High='High Risk';

run;

proc print data=comb_rate label;

FORMAT BMI bmifmt.;

run;

Re: Add a certain number of days onto a date

TimArm — Tue, 30 Apr 2013 13:01:06 GMT

I would do it like this...

data test;
attrib fromdt length=8. format=DATE9.
todt length=8. format=DATE9.
;

fromdt = "03MAR2010"d;

todt = intnx('DAY',fromdt,60);

run;

Re: how to calculate the max length of an observarion and assign it the max maxmium length ...

TimArm — Thu, 20 Dec 2012 14:09:30 GMT

Hello ajuhack,

I have attached 3 macros that you might like to try. The main macro is called %read_csv_data.sas - it calls the other 2 macros.

I have learned by experience not to trust proc import for the very reason that it uses guessingrows. If you are guessing, you will always run the risk of data truncation.

My solution is to perform 2 passes (in the way Cynthia suggested).

Actually, because of my environment, it also reads the top 100 lines first, before doing the full reads, to determine if lines are CRLF or LF terminated!

It searches the top 100 lines for a header record, in order to work out column names.

It searches for a delimiter, or you can specify it if you know it.

It reads all of the data into character variables - so you will need to convert numerics and dates afterwards.

In my test file of 95,000 obs it takes about 1 minute to run on my AIX server.

My other test file of 410MB and over 10 million obs takes 8 or 9 minutes. SAS is good like this - the first full read of the text file takes 7.5 minutes, and the second read takes 40 seconds.

I hope you find this useful.

Tim.

Re: excel to SAS dataset in UNIX

TimArm — Thu, 06 Dec 2012 14:27:29 GMT

I also agree with SASKiwi; I cannot use proc import with Excel files on my AIX server.

If you are using a data step, you could try using the termstr=CRLF option on the infile statement - if your file was saved on a Windows platform.

SandorSzalma is correct that you can still use proc import on unix, as it can still be used to read text files (delimited).

HOWEVER, if you use proc import with a tab delimited file it only looks at the first 2 rows by default to guess variable lengths. You can change this by using the GUESSINGROWS= option.

Re: Change Variable order

TimArm — Thu, 06 Dec 2012 10:28:20 GMT

To try to answer your original question, have you tried this?...

data out;

set ordered (obs=0);

...

run;

If you include the ordered dataset as the first in the set statement, but with obs=0, it should define the variables you want in the order you want.

Re: How can I combine 2 columns into one?

TimArm — Tue, 27 Nov 2012 15:56:36 GMT

Actually, I suggested another approach...but I don't have time to time it

Re: How can I combine 2 columns into one?

TimArm — Mon, 26 Nov 2012 13:09:22 GMT

This should run as quickly as Tom's SQL, I hope (although it is more verbose!):

data lastonly (drop=t_:);

set have (rename=(ColA=t_A ColB=t_B ColC=t_C));

by Period;

length ColA ColB ColC 8.;

retain ColA ColB ColC;

if first.Period then do;

ColA = .;

ColB = .;

ColC = .;

end;

ColA = max(ColA,t_A);

ColB = max(ColB,t_B);

ColC = max(ColC,t_C);

if last.Period then output;

run;

Re: error in %include

TimArm — Mon, 26 Nov 2012 10:17:12 GMT

In that case, if you want to reset the syserr value each time, change the %runquit macro to:

%macro runquit;

%* Reset the syserr value;

data _null_;

x=x;

run;

%mend runquit;

Re: Please Help with the error on Array "ERROR: Array subscript out of range at line ~ column ~ . "

TimArm — Thu, 22 Nov 2012 09:59:50 GMT

min(7,income_dx) - that's an Astoundingly good suggestion. I like it.

I have another alternative, though - use a format to map the value of income_dx to an array index.

With a format you could catch values for income_dx that are not in 1,2,3,4,5,6,9 and assign them to another array index for data "errors". A format would also let you convert the character income_dx to a number without creating another variable.

Re: Please Help with the error on Array "ERROR: Array subscript out of range at line ~ column ~ . "

TimArm — Wed, 21 Nov 2012 16:25:21 GMT

Your earlier post said the income_dx values were 1,2,3,4,5,6, and 9

If you want to do coding4(income_dx) = 1;, then that is going to put something in coding(9) at some point.

Have you got somewhere for sas to put it?

Go back to declaring your array as salary1-salary10 and there will be a variable to store for coding(9).

Then you also need to make sure that you are looking at all of the values when you are retrieving/initialising the values i.e. do k=1 to 10; NOT do k=1 to 7;

Does that make sense?

Re: Please Help with the error on Array "ERROR: Array subscript out of range at line ~ column ~ . "

TimArm — Wed, 21 Nov 2012 15:57:57 GMT

Then you also need do k=1 to 10; as well!

Re: Please Help with the error on Array "ERROR: Array subscript out of range at line ~ column ~ . "

TimArm — Wed, 21 Nov 2012 15:55:21 GMT

9 is bigger than the size of your array!

Instead of array coding4 {*} salary1-salary6 salary7;

try array coding4 {10} salary1-salary6 salary7;

Re: Change column width

TimArm — Wed, 21 Nov 2012 15:53:26 GMT

My advice is this: define the layout (I usually use the attrib statement) that you want in a separate file which you can then include before the set statement e.g.

data ds2;

%include programs(layout_ds2);

set ds1;

...

run;

data ds3;

%include programs(layout_ds3);

set ds2 (rename=(dsvar=tmp_dsvar));

...

dsvar = tmp_dsvar;

run;

Does that make sense? You can control drops and keeps in this way too.

Re: Please Help with the error on Array "ERROR: Array subscript out of range at line ~ column ~ . "

TimArm — Wed, 21 Nov 2012 15:41:59 GMT

What you need to check is: are any of the values of income_dx outside the size of the array i.e. <1 or >7 ?