jwsquillace Tracker

Re: Count number of observation macro won't work on huge table

jwsquillace — Tue, 02 Jul 2013 19:07:59 GMT

From your description, it sounds like the observation counter has overflowed and cannot accurately contain the number of observations in the SAS data set. This limits access to sequential, prevents indexing and generally turns your data set into a giant tape file.

Here is an option:

*IF* you are running SAS 9.3 or later, you can recreate your SAS data set using the data set option like this:

data newdataset(EXTENDOBSCOUNT=YES);

set olddataset; run;

I highly recommend using a different data set name, not reusing the original data set name.

The new copy of the data set will have a different internal structure than the original, but will allow you to query the number of observations, create indexes and access the data set randomly using POINT= option.

The down side is that the new data set cannot be read by SAS 9.2 or earlier releases due to the internal structure change.

Re: search long hyphen (−) in the program

jwsquillace — Thu, 30 May 2013 14:20:55 GMT

The character printed has more to do with the font than anything.

Here is your sample that shows searching for both a long dash or a short dash.

The results show that only the short dash appears in the text, but that may have been messed up by the editor.

/* test2.sas */

data abc;
informat string $150.;
   input id string &;
pos_long = index(string, '96'x);       /* long dash */
pos_shrt = index(string,'2D'x);        /* short dash */
put string= / string $hex100. /;       /* display hex of first 50 characters in log */
cards;
1 patient’s hemoglobin was less than or equal to 11 (units not provided). On an unknown date in May 2013
2 A former school cricket-coach was jailed for 15 months on Thursday for normal@range.
3 deleted stuff (5-6 c/sec) on right frontal derivations corresponding to the lesion site
4 The neurological examination at'admission showe'd a stuporous state with partial cluster seizures
5 The Singapore Armed Forces (S-A-F) will conduct military exercises in Seletar
;

Jan

Re: Datasets different rows value with time calculation.

jwsquillace — Tue, 28 May 2013 14:18:29 GMT

The LAG function works from neighboring observations, so you want to have all the observations for a category together in the input data set.

If the data set is very large, you may find it more efficient to use PROC DATASETS, MODIFY and INDEX CREATE to avoid having to physically rearrange the observations.

Cheers,

Jan

Re: Datasets different rows value with time calculation.

jwsquillace — Fri, 24 May 2013 19:13:06 GMT

Here is sample code that calculcates time difference and previous checkin values. You didn't say about across different categories, so I guessed and reset the previous values to missing.

The test_table appears to be tab-delimited, so I allowed for that, too.

The key is to write the observation before assigning the current values to the previous value variables.

data test;
infile 'c:\temp\test_table.txt' truncover firstobs=2 dlm='09'x;
input Time :time5. Categories :$5. Checkin :$1. ;
length prev_category $5;
retain prev_checkin ' ' prev_time . prev_category ' ';

if categories ne prev_category then do;    /* start of new category? */
prev_time = .;
prev_checkin = ' ';
end;
timediff = time - prev_time;           /* calculate difference */
output;                                /* write observation */
prev_checkin = checkin;                /* save values for comparison with next row */
prev_time = time;
prev_category = categories;
format time prev_time timediff time5.;
keep time categories checkin prev_checkin timediff;
run;

proc print data=test; run;

Jan

Re: Arrays

jwsquillace — Fri, 24 May 2013 18:49:15 GMT

SAS sets DATA step variables to missing between iterations of the DATA step.

Your code indicates that you want them set to missing for each iteration of the DO loop, that you must do with assignment statements as you have done.

Here is more information on the details of DATA step execution:

SAS(R) 9.3 Language Reference: Concepts, Second Edition

Processing a DATA Step: A Walk-through

Jan

Re: Server Connection Not Reading xlsx file

jwsquillace — Fri, 24 May 2013 18:39:48 GMT

The libref has to be 8 or fewer characters, but that is what you have....

Do you get any messages about the success/failure of the LIBNAME statement?

Try putting RUN; after the LIBNAME, run PROC CONTENTS and see if the library connection is working.

Another possibility -- is there a macro-populated range in the workbook? If yes, you may have to open the file in Excel first and force the pointers to resolve to actual values, save the value, then read it in SAS.

Good luck!

Jan

Re: How to Remove encryption for a macro ?????

jwsquillace — Fri, 24 May 2013 17:57:17 GMT

Sanjeev -

I am very sorry to have to give you the news -- stored compiled macros cannot be restored again, especially with the SECURE option.

The point to the SECURE macro option is to protect the source code.

SAS(R) 9.3 Macro Language: Reference

CAUTION:

Save your macro source code.

You cannot recreate the source statements from a compiled macro. Therefore, you must save the original macro source statements if you want to change the macro. For all stored compiled macros, you should document your macro source code well. You can save the source code with the compiled code using the SOURCE option in the %MACRO statement or you can save the source in a separate file. If you save the source in a separate file, it is recommended that you save the source code in the same catalog as the compiled macro.

In this example, save it to the following library: mylib.sasmacro.myfiles.source

Jan

Re: Determining number of variables in a dataset

jwsquillace — Fri, 24 May 2013 15:59:57 GMT

In addition to the dictionary tables, you can open the attributes of the data set in a DATA Step.

If you are going to work with arrays, you may want to separate the character from the numeric variables.

Here is sample code:

%let indsn=sashelp.class;

data _NULL_;
length varnme $32 varClst varNlst $32767;
dsid = open("&indsn");                 /* open data set attributes */
vars = attrn(dsid,"NVARS");            /* get number of variables */
do i = 1 to vars;                      /* go through variable names */
varnme = varname (dsid, i);          /* name of variable */
vartype = vartype(dsid,i);           /* Character or numeric? */
if vartype = "C" then
    varClst = catx(' ',varClst,varnme);
else
    varNlst = catx(' ',varNlst,varnme);
end;
cntC = count(strip(varClst),' ') + 1;
cntN = count(strip(varNlst),' ') + 1;
call symputx('varClst',varClst);         /* put variable list in macro variable */
call symputx('varNlst',varNlst);         /* put variable list in macro variable */
call symputx('varCcnt',cntC);            /* put variable count in macro variable */
call symputx('varNcnt',cntN);            /* put variable count in macro variable */
rc = close(dsid);                      /* close data set attributes */
run;

%put _user_; /* verify existence of macro variables */

Re: Code/algorithm to determine if a date/time was during Daylight Savings Time

jwsquillace — Fri, 24 May 2013 15:44:38 GMT

Here is a published sample that accounts for before and after 2007:

Sample 24735: Compute daylight saving time

http://support.sas.com/kb/24735

Cheers, Jan

Re: SAS error

jwsquillace — Thu, 09 May 2013 14:44:12 GMT

Here is one suggestion:

launch the command prompt "as admin"

cd to C:\program file\sashome\x86\SASEnterpriseGuide\5.1

>regtasks.exe /register

Good luck

Jan

Re: Having issue with representation of decimal

jwsquillace — Tue, 07 May 2013 21:10:22 GMT

SAS handles up to 15 digits of significance in numeric variables.

Here are documentation links you may find useful:

http://support.sas.com/techsup/technote/ts654.pdf
http://support.sas.com/techsup/technote/ts230.html

Jan

Re: Macro statement %put and log

jwsquillace — Tue, 07 May 2013 20:58:13 GMT

is the MPRINT option turned on?

Here's the option statement I use when debugging macro code:

options source source2 symbolgen mprint mlogic;

Jan

Re: Leave 3 best results in table

jwsquillace — Fri, 03 May 2013 17:43:42 GMT

data test;
infile datalines truncover;
input Name $     Age : ;
datalines;
Tom        15
Garry       14
Susan     14
Paul        13
Shaun     12
;

proc sort data=test;
by descending age;

data result;
set test (obs=3);
run;

Re: Merging two data sets

jwsquillace — Thu, 02 May 2013 17:28:08 GMT

Make sure that the variables from the freekids data set do not also exist on the s1net data set.

When we get this question in Tech Support, 90% of the time the answer is extraneous variables on the second data set.

If variables on s1net that match variables on freedkids data set and have values, you may need to do a merge and test for missing value. Here is one possibility:

data test1;
infile datalines truncover;
input id var1 var2;
datalines;
1 1 1
2 2
3 3 3
4 4
;

data test2;
infile datalines truncover;
input id var2 var3;
datalines;
1 11 11
2 21 21
3 . 31
4 41 41
5 51 51
;

data result;
merge test1 (in=in1) test2(in=in2 rename=(var2=var22));
if in1 then do;
if in2 then do;
if var2 = . then var2 = var22;
end;
end;
else if in2 then var2 = var22;
run;

proc print data=result; run;

Cheers,

Jan

Re: CSV file creation assistance needed

jwsquillace — Fri, 26 Apr 2013 13:52:49 GMT

Here is SAS code that demonstrates how to use a DATA step to generate a CSV file with two lines for heading

data _NULL_;

set sashelp.class; /* replace with your data set name */

file 'c:\temp\test.csv' dlm=',';

if _N_ = 1 then do;

put 'first line heading'; /* replace with your heading */

put 'name,age,sex,height,weight'; /* replace with your variables */

end;

put name age sex height weight; /* replace with your variables */

run;

If you have more then 256 characters per output line, you need an LRECL parameter on the FILE statement.

I invite you to send email to support.sas.com if you have further questions.

Jan

Re: Issue with filename statement in DDE

jwsquillace — Tue, 23 Apr 2013 15:10:15 GMT

Referencing the DDE External File

SAS(R) 9.3 Companion for Windows

The syntax shown for DDE in the Windows companion is:

filename test dde 'winword|"c:\temp\testdde.doc" !NUMBER' notab;

Notice the double quotes around the filename inside the single quotes enclosing the DDE triplet.

Jan

Re: base sas

jwsquillace — Wed, 03 Apr 2013 19:44:23 GMT

/* test.sas */

data have;
input var $ &;
cards;
23,32
12.32
32 42
23ab
$23
123
1,5
2.6
1,2,3
1,2.4
1.2.5
;

data required error_data (drop=varN);
set have;
cnt = countc(var,'.,'); /* count periods and commas */
if cnt in (0 1) then do; /* may be valid */
tmpC = translate(var,'.',','); /* change comma to period */
tmp = input(tmpC,??best12.);    /* can be numeric? */
end;
else
tmp = .;   /* missing value means invalid input */
if tmp = . then
output error_data; /* write to error file */
else do;
varN = round(tmp); /* find nearest integer */
output required;   /* write to OK file */
end;
drop tmp tmpC cnt;
run;

proc print data=required; title "Nearest integer";run;
proc print data=error_data; title "Contains invalid characters ";run;

***********************

cheers,

Jan

Re: SET OR APPEND?

jwsquillace — Wed, 03 Apr 2013 19:12:56 GMT

I search support.sas.com for "proc append samples"

Here are some that may be applicable:

Sample 48810: Append all SAS data sets in a SAS library into 1 SAS data set when the number of SAS data sets varies

http://support.sas.com/kb/48810

Sample 45143: Error trapping with PROC APPEND

http://support.sas.com/kb/45143

Sample 25291: Adding new observations to a SAS data set using PROC APPEND

http://support.sas.com/kb/25291

Sample 33407: Combining Data Sets Containing Character Variables of Different Lengths

http://support.sas.com/kb/33407

One of these may give you ideas to get started.

Cheers,

Jan

Re: infinite do-while loop

jwsquillace — Fri, 29 Mar 2013 15:29:35 GMT

The root of the issue is that macro references are resolved once before the DATA step is compiled.

Your code attempts to change the variables referenced by changing the values of the macro variables.

This demonstrates the general idea of what I am attempting to describe. The SAS code runs without syntax errors, but there are no guarantees that it is correct. I will leave that to your expertise.

/* test1.sas */

DATA data;
INPUT President $ Party $ X pf1_20 pf1_40 pf1_60;
DATALINES;
Adams F 2 10 20 30 40
Lincoln R 16 10 20 30 40
Grant R 18 10 20 30 40
Kennedy D 35 10 20 30 40
Adams F 4 10 20 30 40
Lincoln R 17 10 20 30 40
Grant R 22 10 20 30 40
Kennedy D 35 10 20 30 40
Adams F 8 10 20 30 40
Lincoln R 11 10 20 30 40
Grant R 19 10 20 30 40
Kennedy D 40 10 20 30 40
;
RUN;
%let startperc1=20;
%let endperc1=60;
%let stepperc1=20;
%let sortingvar1=X;
%let nowperc=%eval(&startperc1);
%let prevperc=%eval(&startperc1-&nowperc);
data newdata;
    set data;
nowprec = &nowperc;
prevperc = &prevperc;
array pf1 (*) pf1_0 pf1_20 pf1_40 pf1_60;
pf1_0 = 0;
pnt = 1; /* pointer for pf1 array */
do while (nowperc lt &endperc1);
   if &sortingvar1>=pf1(pnt) AND &sortingvar1<pf1(pnt + 1) then
     rank = (nowperc - &startperc1 ) / ( &stepperc1 +1);
   nowperc + &stepperc1;
   prevperc + &stepperc1;
   pnt + 1;
end;
drop pnt pf1_0;
run;

Cheers,

Jan

Re: SET OR APPEND?

jwsquillace — Fri, 29 Mar 2013 14:42:22 GMT

In general, PROC APPEND is more efficient because it merely opens the base data set and adds the additional observations to it.

When you use SET to concatenate data sets, as here:

data base;

set base newdata;

run;

you are rewriting all the base observations in addition to the new data. When your data sets are small, this is not important. However, as the base data set gets larger, you wll find the process taking longer and longer.

As Art Carpenter noted, the SET method allows you to apply program logic to the concatenation and is far more flexible than PROC APPEND.

If speed and efficiency are critical, and you simply want to add the observations, use PROC APPEND.

If you require flexibility and program logic, use SET statement.

If you want to summarize statements on key variable(s), use MERGE statement as suggested by @ghastly_kitten

Cheers,

Jan