RichardinOz Tracker

Re: Can anybody explain me why we use '||' in call %execute() routine?? below is the given problem

RichardinOz — Sun, 14 Sep 2014 12:41:26 GMT

|| is the concatenation operator in SAS. In Excel you would use &.

Call execute(<code to be executed>) allows SAS to place a section of code into the program flow to be executed immediately after the data step concludes. In this case the code to be executed is a macro rept which is intended to draw a vbar chart on var1 for a given date drawing data from the reptdata table. The data _null_ step is intended to provide the date parameter from a table called dates. Lets say the date, being numeric, has the unformatted value 12345 (I have no idea what date that is). Then the code inserted into the program flow should be

%rept(12345,var1,reptdata)

To get this result the data _null_ step has to join some constant text values: %rept( and then later ,var1,reptdata) with the date value 12345 taken from the dates table in the middle. The expression could be simplified as

call execute('%rept(' ||date|| ', var1, reptdata)'); /* spaces added for clarity */

since apparently for this example var1 from reptdata is always used when table dates is used as the source of date data.

Richard

Re: SAS packaged solutions outstrips need for SAS developers?

RichardinOz — Fri, 12 Sep 2014 13:30:51 GMT

As an afterthought:

Not every SAS admin can be a Jaap of all trades.

Richard

Re: Import only values from Excel worksheet.

RichardinOz — Fri, 12 Sep 2014 13:27:14 GMT

In your example 2.333......333 is the unformatted (underlying) value in Excel, which is what SAS reads and imports. You can apply a SAS format such as 16.1 or comma20.1 after the import to achieve what you want.

The standard import procedure in SAS does not carry across the format applied in Excel, although in the case of dates SAS uses the Excel format to identify the value as a date, import it and adjust the base date and apply a SAS date format which might or might not look similar to the Excel format.

You might be able to achieve what you want by exporting the data as a text file (CSV or tab delimited) and importing that into SAS.

Richard

Re: SAS packaged solutions outstrips need for SAS developers?

RichardinOz — Fri, 12 Sep 2014 13:01:18 GMT

Cold windy and wet down here but at least I can see where I am heading as I stumble off to work.

As it happens I am working for an organisation which is implementing a SAS solution and there seems to be no shortage of opportunities to ply my trade. No doubt that as the products mature over time some of the 'features' will be smoothed over but the SAS admins have quite enough to do, thank you, without having to get involved in model validation, to take an example.

I have also recently worked in an organisation which has decided on an in house approach to the same type of development. I think the potential value of the package approach lies in the nature between the organisation and the vendor: it can and should be much more of a partnership than a transaction. My analogy is the doctor and the pharmacist: my doctor understands (some of) what makes me tick, the pharmacist knows my name because it is on the scrip. SAS can sell you the 'pills' for you to use as you see fit, or they can help diagnose and address the business requirements. And when it is all implemented inevitably people move on, diluting the intellectual capital of the organisation, but some of that is offset by having a business partner that continues to understand both the issues and the solution.

Richard "too soon to bring out the budgie smugglers"

Re: SAS packaged solutions outstrips need for SAS developers?

RichardinOz — Wed, 10 Sep 2014 11:35:25 GMT

Well Charlotte

If every bank in the world was running identical banking applications on identical machines using identical identifiers and marketing identical products to identical customers using identical business rules in identical regulatory requirements and having identical maverick board members asking identical maverick questions about risk you might have a case that an out of the box credit risk solution could be implemented by a couple of admin, at least until the world suffers its next identical financial crisis and all the rules change again.

In the real world sleepless tossing is not advised.

In any case you can switch to marketing or fraud detection where it is the nature of success to innovate or at least attempt to keep up with what others are doing, requiring inventiveness and flair which will not come ot of a box.

Richard

Re: extracting first few digits from a numeric variable

RichardinOz — Sun, 07 Sep 2014 07:20:19 GMT

Wot???

The formula is entirely numeric, intended to operate on a numeric variable, as described by the OP.

As it happens I am in agreement with you about automatic conversions.

Apology accepted

Richard

Re: Joining two tables based on observations in a seperate table (in EG)?

RichardinOz — Fri, 05 Sep 2014 23:21:20 GMT

How are you going to do the merge? You need some common variable in each table to join on to avoid a cartesian product in SQL. If there is none a datastep merge without a by variable might work for you though the result may not be quite what you expect.

Step 1: set up a macro to do the merge - here assuming no common variable

%macro merge_ctry (ctry, crncy) ;

data &ctry._combined ;

merge ctry_&ctry

crncy_&crncy

;

run ;

%mend ;

Step 2: use a datastep to execute the macro for each ctry - crncy pair

data _Null_ ;

set countries ;

if exist(ctry) and exist(crncy) ;

exec_macro = cats('%merge_ctry(', ctry, ', ', crncy, ');') ; /* single quotes essential */

call execute (exec_macro) ;

run ;

NB the macro invocations will be streamed to execute after the data step terminates.

Obviously this is untested but it is a viable approach.

Richard

Re: extracting first few digits from a numeric variable

RichardinOz — Fri, 05 Sep 2014 22:58:15 GMT

Here is a solution that avoids number to character conversion and back again, and also deals with fractional and negative values

int(abs(num)/10**(log10(abs(num))-3))

It works by dividing the number by the requisite power of 10 (including negative power) and truncating the decimal portion.

Richard

Re: reading data, expanding to seperate variables

RichardinOz — Tue, 26 Aug 2014 12:50:34 GMT

Here is a solution which will write the original 'code' and its expansion to the log. It can be adapted to write to a file. Fortuitously it deals with the miscode spotted by Data _Null_. Next step named in honour.

Data _null_ ;

input codes $char60. ;

length code base range result $8 ;

codes = compress(codes, "'") ;

Do k = 1 to 99 ;

code = scan (codes, k, ', ') ;

if missing(code) then return ;

range = scan (code, 2, '.') ;

base = scan (code, 1, '.') ;

m = input (scan (base, 1, 'C-'), 2.) ;

n = input (scan (base, 2, 'C-'), 2.) ;

p = input (scan (range, 1, '-'), 1.) ;

q = input (scan (range, 2, '-'), 1.) ;

if missing (range) then

do ;

p = 0 ;

q = 9 ;

end ;

put code '> ' ;

do j = m to max (m, n) ;

base = cat ('C', put (j, z2.)) ;

do i = p to max (p, q) ;

result = catx ('.', base, i) ;

put result @ ;

end ;

put ;

end ;

drop codes i j k m n p q base range ;

Datalines ;

'C00.3-5, C02-04, C05.0, C05.8-9, C06'

'C01, C10.0, C10.2-3, C10.8-9, C11-12'

'C01, C05.1-2, C09, C10.0, C10.2-3, C10.8-9, C11-14'

'C44.0-4, C47.0, C49.0, C00-14, C30-32, C73'

'C44.5, C44.8-9, C47.6-9, C49.6-9'

'C00-C26, C32-C69, C73, C75.4-9, C76-C80'

'C00-50, C60-61, C63-69, C73-74, C76-80'

'C00, C03-08, C15-17, C19-41, C44-76'

run ;

Richard

Re: run code only in january

RichardinOz — Sun, 24 Aug 2014 14:58:37 GMT

where month(reporting_date) = 12

Richard

Re: mod function

RichardinOz — Sun, 24 Aug 2014 14:56:13 GMT

Assuming you have the date as a macro variable you could create

%sysfunc(mod(%sysfunc(month(&Date)), 2))

for a 0 , 1 even - odd

or %eval(2 - [the expression above]) to have 1, 2 with 1 odd , 2 even

Richard

Re: Procs in SAS 9.1

RichardinOz — Thu, 14 Aug 2014 12:56:24 GMT

If you don't have Proc REG you either do not have the SAS STAT product installed or it is damaged. You need SAS STAT to do multivariate analyses. In Base SAS you are limited to univariate analyses and correlations.

Richard

Re: Working with SAS format dates

RichardinOz — Thu, 14 Aug 2014 12:38:49 GMT

Linus' reply is a bit terse. The intnx function will provide an answer, provided you supply the right question. Do you want 9 calendar months exactly (ie to the same day - if possible - 9 months previously), or do you just need the month and the first day will do; or maybe push all dates back by the same amount (0.75 year = 274 days)?

Taking these options in reverse order:

Prev_date = date - 274 ;

Prev_date = intnx("MONTH", date, -9) ;

Prev_date = intnx("MONTH", date, -9, "SAME") ;

INTNX is documented here

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

BTW, if your dates are datetime intervals you need to use "DTMONTH" instead of "MONTH" ; and for the 274 day calculations use

Prev_date = intnx("DTDAY", date, -274, "SAME") ;

Richard

Re: Multiple do loops in a data do not return what was expected

RichardinOz — Fri, 01 Aug 2014 10:43:59 GMT

Try running your code but including the missing statement after the data statement. "missing" is a global statement that notifies SAS that your code handles special missing values.

Richard

Re: I am trying to call excel macro from sas 9.3 EG connection from my local server. But I am getting the following error in the initial step itself of opening a excel. I am new to SAS DDE, please help me

RichardinOz — Wed, 30 Jul 2014 12:13:05 GMT

Microsoft DDE only works when both the application issuing the DDE statements and the target application are on the same Windows platform. Typically, when using SAS EG, the core SAS application resides on a server (which may or may not be a Windows server) but the Excel instance is on a local machine. SAS EG, even though it is located locally, is not itself involved in DDE; it merely issues SAS code to the server to be executed. Use EG file import/export to move data between Excel and SAS.

Despite its many merits, (and many champions), DDE is legacy technology which, as you have found, is not scalable in a client server (EG) situation. Microsoft and others did toy with the idea of making it more generic but it didn't happen and we now have to find repoacement methods.

I have some sympathy for you if you have been given some code to run. You can only make DDE work if you have Base SAS (and Excel) installed on your desktop, or you can adapt the code to work on a Windows server where you have SAS. If your workplace can provide that, good. Otherwise you have been set a task you cannot complete.

Richard.

Re: Multiple do loops in a data do not return what was expected

RichardinOz — Tue, 29 Jul 2014 10:49:56 GMT

The missing statement is missing! Your code works unchanged following

data temp1 ;

missing A B C D E F G ;

infile datalines missover dlm = '09'x ;

input Obs C03 C06 C08 C05 C07 C02 C01 C04 temp1 temp2 r1 r2 ;

drop obs r1 r2 ;

datalines ;

1 1 1589691 1385 6 4 74 98.564 5.247 0.8 0.2 60 103

2 1 831697 445 4 5 91 112.11 6.0543 0.2 0.7 103 103

3 C 438287 170 15 5 67 142.051 12.3561 0.1 0.5 12 57

4 4 513424 151 3 4 26 145.345 12.1783 0.1 0.3 12 67

5 1 632787 549 3 3 71 110.274 7.9074 0.2 0.8 103 2

6 1 784585 138 17 3 36 96.169 12.9356 0.5 0.4 57 57

7 0 5220856 611 7 5 53 120.258 8.8338 0.7 0.8 103 2

8 E 333766 456 5 4 60 134.078 5.308 0.1 0.2 12 103

9 C 432310 525 5 3 61 129.407 7.8711 0.1 0.2 12 103

10 C 8509368 2390 13 6 56 141.523 3.8929 0.1 0.5 12 57

;

Result

Obs	C03	C06	C08	C05	C07	C02	C01	C04	temp1	temp2	r1	r2
1	1	1589691	1385	6	4	74	98.564	5.2470	0.8	0.2	60	103
2	1	831697	445	4	5	91	112.110	6.0543	0.2	0.7	103	103
3	C	438287	170	15	5	67	142.051	12.3561	0.1	0.5	12	57
4	4	513424	151	3	4	26	145.345	12.1783	0.1	0.3	12	67
5	1	632787	549	3	3	71	110.274	7.9074	0.2	0.8	103	60
6	1	784585	138	17	3	36	96.169	12.9356	0.5	0.4	57	39
7	0	5220856	611	7	5	53	120.258	8.8338	0.7	0.8	103	60
8	E	333766	456	5	4	60	134.078	5.3080	0.1	0.2	12	103
9	C	432310	525	5	3	61	129.407	7.8711	0.1	0.2	12	103
10	C	8509368	2390	13	6	56	141.523	3.8929	0.1	0.5	12	57

Richard

Re: Multiple dummy variables to one dummy variable

RichardinOz — Mon, 28 Jul 2014 12:33:49 GMT

Data want ;

Set have ;

outcome = max (anxiety, stress, multiple, etc1, etc2, etc3, etc4) ;

Run ;

Richard

Re: Help Padding Blanks

RichardinOz — Wed, 16 Jul 2014 11:15:30 GMT

SAS does not "forget" the trailing blanks. However, when making a comparison between two character variables as a convenience it in effect trims (removes trailing blanks) for the purpose of making the comparison and only during the comparison.

The alternative would be that you would have to extend the shorter variable with blanks in order to compare two variables of unequal length. Maybe this is what SAS is doing "under the covers" anyway. The effect is the same.

This is a convention that has been adopted by SAS and I think they made the right choice. It would be tedious to have to check the length of each variable before making a comparison, in case the comparison would fail each time the lengths were different.

An alternate design would have used byte(0) ( '00'x ) for padding in which case the number of trailing blanks would not change if the value is copied into a variable with longer length, and would be significant in comparisons. It's one of the early design decisions that become fundamental to the language.

You can see that SAS retains trailing blanks

data test ;

length var $ 12 ;

var = 'ABC' ;

var = var || 'XYZ' ;

put var= ;

run ;

Because var is defined with length 12 the value ABC is padded with 9 blanks. It then has no space left to add on the XYZ.

Macro variables, on the other hand, do not have fixed length and they can be extended, so you get a different result:

%Let var = %Str(ABC ) ;

%Let var = &var.XYZ ;

%Put &var ;

Richard

Re: increment by subject using retain in a single datastep

RichardinOz — Sat, 05 Jul 2014 04:13:58 GMT

Jag

Your main problem is you are entering month as a name rather than as a date, so the months will not sort properly. The solution above works for a single subj provided all the months are in the same year and the data is in intuitive order. To generalise to more than one subject you need to reset the counter for each subj.

data want;

set have;

by subj month notsorted;

retain seq ;

if first.subj then seq=0 ;

if first.month then seq+1;

run;

Richard

Re: Create new observation from 'or' statement

RichardinOz — Sat, 21 Jun 2014 08:01:05 GMT

Here is a one step solution

data have;

Infile cards missover ;

input id x $ @ ;

output ;

input @'or' x ;

If not missing(x) then output ;

cards;

1 a

2 b

3 a or b

;

run ;

Richard