All SAS Procedures posts

Re: Accept Self-signed certificate with PROC HTTP

valengvz — Thu, 22 Jul 2021 16:10:10 GMT

Thank you so much, it works for now!

Re: Accept Self-signed certificate with PROC HTTP

ChrisHemedinger — Thu, 22 Jul 2021 16:00:28 GMT

Why not skip creating the file and just put the literal value in the IN=, at least for now? Your value (which isn't JSON anyway) might have extra quotes from your %LET statement.

Re: Accept Self-signed certificate with PROC HTTP

valengvz — Thu, 22 Jul 2021 15:46:59 GMT

I'm using your code but it seems like there is a problem with the credentials, this is the way I created the jsonin file, I already correct the PROC HTTP statement as well

filename jsonin temp lrecl=32760;
filename jsonout temp lrecl=32760;
filename headout temp lrecl=32760;

%let string= 'grant_type=password&username=client&password=xxxx';

data _null_;
	call symputx('FLEN', length(&string));
run;

data _null_;
	file jsonin recfm=F lrecl=&FLEN;
	put &string;
run;


proc http url="&server_host/&server_endpoint_token"
    in=jsonin
    webusername="client"
    webpassword="cleintsecret"
    CT="application/x-www-form-urlencoded"
    out=jsonout
    headerout=headout;
	debug level=3;
    headers "Accept"="application/json" ;
run;

Re: Accept Self-signed certificate with PROC HTTP

ChrisHemedinger — Thu, 22 Jul 2021 15:21:21 GMT

Some tweaks to the way you transcribed the cURL to PROC HTTP:

proc http url="&server_host/&server_endpoint_token" in="%nrstr(&grant_type)=password%nrstr(&username)=user" webusername="client" webpassword="clientsecret" CT="application/x-www-form-urlencoded" out=jsonout headerout=headout; debug level=3; headers "Accept"="application/json" ; run;

If you share more about which service you're using (if it's something common), we might have a better example.

the IN= takes what you specify in cURL -d option. The webusername/webpassword takes what you specify in the -u option. CT= is shorthand for "Content-Type" so no need to include the "Content-Type=" as part of the value.

The SSLPARMS statement can help specify behavior for verifying certificates. Adding the SSLREQCERT= value may help.

Something like:

sslparms "sslreqcert"="allow";

Accept Self-signed certificate with PROC HTTP

valengvz — Thu, 22 Jul 2021 14:49:37 GMT

Hi there, I'm trying to translate this code into SAS code with PROC HTTP

curl --insecure -k  https://server/oauth/token -H "Accept: application/json" -H "Content-Type: application/x-www-form-urlencoded" -d "grant_type=password&username=user&password=xxxx" -u client:clientsecret"

So far, I have this SAS code

proc http url="&server_host/&server_endpoint_token"
    in=jsonin
    webusername="client"
    webpassword="clientsecret"
    CT="Content-Type=application/x-www-form-urlencoded"
    out=jsonout
    headerout=headout;
	debug level=3;
    headers "Accept"="application/json" ;
run;

The problem is that I have to accept the self-signed certificate but I don't know how to implement that in the PROC HTTP procedure.

Does someone know how to do that?

Re: proc lifetest Brookmeyer-Crowley confidence interval for median: conftype=?

sbxkoenk — Wed, 21 Jul 2021 23:04:53 GMT

Look here:

Brookmeyer and Crowley Confidence Intervals?
Posted 11-02-2019 11:00 PM
https://communities.sas.com/t5/Statistical-Procedures/Brookmeyer-and-Crowley-Confidence-Intervals/td-p/601198

Kind regards,

Koen

proc lifetest Brookmeyer-Crowley confidence interval for median: conftype=?

fengyuwuzu — Wed, 21 Jul 2021 22:57:08 GMT

since SAS 9.2, the default CONFTYPE=LOGLOG in proc lifetest.

if I want calculate BrookMeyer-Crowley confidence interval for the medial survival, must I set CONFTYPE = LINEAR?

How do I know the confidence interval I got is Brookmeyer-Crowley CI?

Re: t grouping is showing in bar not letter

atikurmamun — Mon, 19 Jul 2021 10:48:10 GMT

dear, i got the solution after using ods graphics off code using. though i didn't get any permanent option for close or off this ods I have to use this in every program for ods graph off. however, i am now happy and this will be ok for me.

Re: t grouping is showing in bar not letter

atikurmamun — Mon, 19 Jul 2021 10:27:53 GMT

yaa i don't want the graph rather i want the letter against graph...i want the picture 2 format not picture 1...
now question is...how do i off ODS graphics off cause i didn't on anything. if you could please tell me where is the option it will be very much helpful...

Re: t grouping is showing in bar not letter

ballardw — Mon, 19 Jul 2021 08:15:18 GMT

The code that created the output would likely be helpful.

I am not sure I understand your question. Are you asking how to not create the graph? If you do not want the graph then you may need to include 1) ODS Graphics off; in your program before the procedure and/or 2) remove any Plot statements.

The ODS Graphics is a system option. If you turn it on in one part of your code you may need to turn it off in another. Depending on your environment for running SAS some of these options will persist from the last setting in a previous session and need to be turned off in the current.

Re: Advanced Certification Sample Question - Array

Smartrp21 — Mon, 19 Jul 2021 03:03:43 GMT

data test;
set sashelp.pricedata;
array oldprice[17] price1-price17;
array newprice[17] price1-price17;
do i=1 to 17;
newprice[i]=oldprice[i]*1.1;
end;
drop i;
run;

t grouping is showing in bar not letter

atikurmamun — Sun, 18 Jul 2021 20:41:03 GMT

i want to know when i see the sas 1st time i see the t grouping comes out with letter and means but nowadays i see it is coming with means and bar diagram though the value is ok for me, it is hard to present in front of someone cause they sometimes couldn't understand the bar but they can understand the lettering.is there anyone who can help me withit is new styleit is old one where grouping comes with lettering this?
i have attached 2 pictures for understanding my question. Number 1 is new and number 2 is the old one. please give me a solution.

Re: OPTION Groups Enterprise Guide versus Windowing Environment

FK1 — Fri, 16 Jul 2021 10:10:12 GMT

Hello ,

this definitely already clarifies things a little bit for me, thank you.

Basically, I was trying to clarify what "type" of Options in the SAS universe exist and what type of values, which can be assigned to these options, exist.

Therefore, I tried to set up an options/option values typology. But it turns out, that this is more difficult than thought, as - in my opinion- the SAS documentation alreay has some ambiguities, when it comes to this topic.

For example, this is the "official" definition of SAS system options

Reading through the documentation of the "getoption" function , it is talked about "system options" versus "graphics options".

Some options can be restricted versus non-restricted,. Also some options are portable and some are host-dependent. Additionally, some options can be saved, other cannot besaved.

Sidenote:

Running

proc options listoptsave;
run;

The log displays "core options" and "Host options" that can be saved....

Of course, some options apply only to certain operating systems.

Furthermore there are "character system option values" versus "numeric system option values" not to mention the "boolean option values".

Long story short: is there a kind of "list" that tells me at one glance all the above mentioned "types of information" with respect to a particular option and its option value?

Re: How to print only first page ODS PDF

Cynthia_sas — Fri, 16 Jul 2021 01:35:55 GMT

Hi:
When you use Adobe Reader to open the PDF file that was created by ODS, why can't you only print page 1 to your printer. Otherwise I'm not clear on what you mean when you say I need to "print it out on PDF". A PDF file is a proprietary file format for Adobe Acrobat "Portable Document Format" files. When you direct your PROC TABULATE output to ODS PDF, all of your TABULATE output will go into the PDF file. PROC TABULATE doesn't really have physical pages. PROC TABULATE sends output to RTF and PDF and those destination viewers have physical pages that you can print. But once you open the file, you are operating with the applications interface (in this case, either Word or Acrobat and their interface to the printer).

Is there a way to cut your TABULATE down into multiple steps so that the first step produces only page 1? Or do you need for all of your output to be created by TABULATE and then you only want to open the first page? That's what is not clear to me about your statement. ODS DOCUMENT might work for you here, but that seems overkill for something that the Acrobat Reader interface can do with a PDF file quite easily, as shown below:

This is a PDF file created with PROC TABULATE and ODS. I only created a report with 15 pages, but it really doesn't matter whether I created 15 or 90 pages since Acrobat Reader allows me to print only the first page.
Cynthia

Re: OPTION Groups Enterprise Guide versus Windowing Environment

Cynthia_sas — Fri, 16 Jul 2021 01:22:26 GMT

Hi:

Well, without knowing what kind of operating system server SAS is running on when you use Enterprise Guide, it's hard to comment. Remember that Enterprise Guide is a client application. It is a front end to SAS where SAS could be on a mainframe, a Unix or Linux box or on a Windows server. Some of the names for your options groups are familiar to me from my old mainframe days, ISPF, REXX, IMS, DB2, those are all mainframe applications. ADABAS is a SoftwareAG database system that I used on a mainframe. DB2 and IMS are IBM databases that I used on a mainframe and also I used DB2 on Unix.

Options are operating system specific. So I would always expect server SAS to have different options than a local Windows install of SAS. My guess is that it's a difference related to operating system. If it really bothers you, then consult with your SAS Systems group or open a track with Tech Support.

Cynthia

Re: SAS Log doesn't update after ctrl-break stopping

bpowell — Wed, 14 Jul 2021 14:20:06 GMT

Something about proc export, which uses ODS under the hood (so I understand) writes to the log in such a way that it bypasses whatever it was in the log break routine that has failed when killing code. Again, this only happens in some cases - sometime if you kill code it dies gracefully and the log is unimpacted. When it does stop updating, there is something about interupting the call to ODS in an export statement that manages to restart the log write.

Re: SAS Log doesn't update after ctrl-break stopping

Tom — Wed, 14 Jul 2021 14:14:30 GMT

But what does pinging the ODS system have to do with whether or not the SAS log is getting updated?

Re: SAS Log doesn't update after ctrl-break stopping

bpowell — Wed, 14 Jul 2021 14:07:46 GMT

Apologies for my slightly cheeky reply and thank you for your good humour.

Interrupting the export does indeed rekindle a frozen SAS log in 9/10 occasions. Afraid I have no idea why it works but it does. I understood it was something to do with pinging the ODS system.

Yes the thread is old, but then it has not been resolved, so it is relevant I think to keep it open.

OPTION Groups Enterprise Guide versus Windowing Environment

FK1 — Wed, 14 Jul 2021 13:08:14 GMT

Hi Everybody,

can someone explain to me, why I get a different number of OPTION Groups (and presumably also a different number of options! ) when runninng PROC OPTIONS in SAS Enterprise Guide like this:

proc options listgroups long; 
run;

which results in the following Log output:

GROUP=ADABAS ADABAS
GROUP=ANIMATION Animation
GROUP=CAS CAS Options
GROUP=CODEGEN Code generation
GROUP=COMMUNICATIONS Networking and encryption
GROUP=DATACOM Datacom
GROUP=DATAQUALITY Data Quality
GROUP=DB2 DB2
GROUP=EMAIL E-mail
GROUP=ENVDISPLAY Display
GROUP=ENVFILES Files
GROUP=ERRORHANDLING Error handling
GROUP=EXECMODES Initialization and operation
GROUP=EXTFILES External files
GROUP=GRAPHICS Driver settings
GROUP=HELP Help
GROUP=IDMS IDMS
GROUP=IMS IMS
GROUP=INPUTCONTROL Data Processing
GROUP=INSTALL Installation
GROUP=ISPF ISPF
GROUP=LANGUAGECONTROL Language control
GROUP=LISTCONTROL Procedure output
GROUP=LOGCONTROL SAS log
GROUP=LOG_LISTCONTROL SAS log and procedure output
GROUP=MACRO SAS macro
GROUP=MEMORY Memory
GROUP=META Metadata
GROUP=ODSPRINT ODS Printing
GROUP=PDF PDF
GROUP=PERFORMANCE Performance
GROUP=REXX REXX
GROUP=SASFILES SAS Files
GROUP=SECURITY Security
GROUP=SMF SMF
GROUP=SORT Procedure options
GROUP=SQL SQL
GROUP=SVG SVG
GROUP=TK TK

versus using the SAS Windowing Environment by opening the SAS Options Environment Window via command line:

and then counting the OPTION GROUPS, that are shown:

In the first case, I counted 39 Option Groups, while in the later case, I only got 31 (I did not count the "Top Folders" like "Communications", "Environment Control", etc. since they do not have any options but only seem to be an aggregation of several Option Groups).

In essence, I am observing a deviance in the sense of not being displayed of the following OPTIONS GROUPS in Windowing Environment:

ADABAS
Datacom
DB2
IDMS
IMS
ISPF
REXX
SMF

Re: How to specify multiple terms in the macro with proc nlmixed

windy — Wed, 14 Jul 2021 12:42:12 GMT

The second version has become so flexible and efficient that I can utilize it in other cases with minor modifications. I can't thank you enough for showing me the good piece of code.

Re: SAS Log doesn't update after ctrl-break stopping

Tom — Wed, 14 Jul 2021 12:32:44 GMT

Not sure I understand your comment (either part).

Are you saying the thread is not from 9 years ago with the most recent posting 4 years ago?

Are you saying that just interrupting a PROC EXPORT will cause the SAS session to start writing to the LOG again? Or did you mean that interrupting a PROC EXPORT is your suggestion of why the original poster (or perhaps this new poster) had their SAS log stop updating?

Either way that is interesting idea but I am not how. Perhaps somehow during the PROC EXPORT SAS is changing some setting that users cannot change directly and interrupting it causes it skip the step that sets it back to normal?

Re: How to specify multiple terms in the macro with proc nlmixed

FreelanceReinhard — Wed, 14 Jul 2021 11:46:20 GMT

wrote:

I was thinking that I can remove the + sign in the first macro and use it later in eta, ...

This is actually a good idea because it makes the macro a bit more flexible (for situations where the plus sign is not appropriate).

So, with the second version of the macro you can make the plus sign an optional part of the first argument in the macro call:

108  %put eta = int %terms(+ a#*D#,5) %terms(+ b#*D#*X,5);
eta = int + a1*D1 + a2*D2 + a3*D3 + a4*D4 + a5*D5 + b1*D1*X + b2*D2*X + b3*D3*X + b4*D4*X + b5*D5*X

Re: How to specify multiple terms in the macro with proc nlmixed

windy — Wed, 14 Jul 2021 11:37:31 GMT

I saw it. By the way, I need the + sign there though.

I was thinking that I can remove the + sign in the first macro and use it later in eta, but the code is not resolved.

Thank you again for your support.

Re: How to specify multiple terms in the macro with proc nlmixed

FreelanceReinhard — Wed, 14 Jul 2021 11:26:57 GMT

The plus sign is just text in the macro. If you remove it,

%macro terms(pattern,n);
%local i;
%do i=1 %to &n;
%sysfunc(tranwrd(&pattern,#,&i))
%end;
%mend terms;

the result will look something like this (log excerpt)

99   %put eta = int %terms(a#*D#,5) %terms(b#*D#*X,5);
eta = int a1*D1 a2*D2 a3*D3 a4*D4 a5*D5 b1*D1*X b2*D2*X b3*D3*X b4*D4*X b5*D5*X

Re: How to specify multiple terms in the macro with proc nlmixed

windy — Wed, 14 Jul 2021 11:22:47 GMT

Sorry for bothering you again

What should I do if I want to remove the + sign?

Re: How to specify multiple terms in the macro with proc nlmixed

windy — Wed, 14 Jul 2021 11:18:04 GMT

Hi ,

This is working beautifully. Thank you so much for helping me again. I really appreciate it.

Re: How to specify multiple terms in the macro with proc nlmixed

FreelanceReinhard — Wed, 14 Jul 2021 10:09:43 GMT

Hello ,

You could write a little utility macro like this (just an example -- feel free to modify it)

%macro terms(pattern,n);
%local i;
%do i=1 %to &n;
+ %sysfunc(tranwrd(&pattern,#,&i))
%end;
%mend terms;

and then write the model like this:

eta = int %terms(a#*D#,5) %terms(b#*D#*X,5);

That is, the wildcard "#" in the first argument of the macro is replaced by numbers 1, 2, ..., n (n=second argument of the macro) and the resulting model terms are concatenated with " + ".

How to specify multiple terms in the macro with proc nlmixed

windy — Wed, 14 Jul 2021 09:16:54 GMT

Hello everyone,

I am seeking help to write a macro using proc nlmixed with multiple terms.

I want to define the model in proc nlmixed with multiple dummies (let say D1, D2, D3, D4, D5) and the interaction terms between variable X with each of those dummy variables.

The code can be written normally as:

proc nlmixed data=have;

eta = int + a1*D1 + a2* D2 + a3*D3 + a4*D4 + a5*D5 + b1*D1*X + b2*D2*X + b3*D3*X + b4*D4*X + b5*D5*X;

......

run;

My actual model is much longer than that since I have up to 9 dummies and 2 variables X. In total, I will need to specify around 30 terms in my model. Is there way to make my model specification shorter which allows me to modify the model easily?

Thank you so much in advance.

Windy.

Re: SAS Log doesn't update after ctrl-break stopping

bpowell — Wed, 14 Jul 2021 08:34:13 GMT

Well done Tom, a text book example of not even begining to address the question. The thread is ancient because it hasn't been answered / resolved. Imagine this: my brake pedal stops working if I drive at 71mph with the windows down and the manufacturer suggests, a) the speed limit is 70mph and I shouldn't be driving at 71mph and b) consider driving with the windows up above 65.

I run this code frequently (and please don't lecture me why I shouldn't be breaking running code - its quite possible I've been using SAS for longer than you've been alive). Hint, this restores a log that has stopped updating,

King Regards.

/*--------------------------------------*/
/*!!!Kill this code during the export!!!*/
/*--------------------------------------*/

data temp;
do i=1 to 1000;
x=1;
output;
end;
run;

proc export data = temp outfile="c:\temp\temp.xls" replace;run;

Re: SAS Log doesn't update after ctrl-break stopping

Tom — Tue, 13 Jul 2021 20:02:56 GMT

This is an ancient thread. If you have a new question open a new thread. You can link back to this one if it helps explain your issue.

That said the main thing I see missing from this thread is no discussion of the impact of stopping a program in the middle that either turning of writing to the log by change options like NOTES or SOURCE or has redirected the log to another place using PROC PRINTTO.

Someone on this thread mentioned that they run a lot of long running program interactively. You should probably learn how to run those programs as regular or background jobs instead of using interactive SAS (or even the new pseudo interactive interfaces like SAS/Studio or Enterprise Guide). Use the interactive tools to develop and test your code on smaller samples of the data. Then save the program and run it from the command line of via some type of "batch submit" option.

Re: SAS Log doesn't update after ctrl-break stopping

Khan2 — Tue, 13 Jul 2021 19:31:54 GMT

I have noticed this issue as well. Unfortunately, hitting "!" occasionaly to stop the code is unavoidable when you are working with big data. This issue has persisted for almost 7 years now.

Re: Lookup table and fill in missing values (ACS data)?

Astounding — Tue, 13 Jul 2021 18:24:54 GMT

There's no source other than what you have (likely) see already. It's the documentation on what the UPDATE statement does.

To keep the variables in the order of the first data set, here's a trick you can use:

data want;
   if 5=4 then set first;
   update second first (in=original_100k);
   by pat_id;
   if original_100k;
run;

Since 5 will never equal 4, the SET statement never executes. But its presence is enough to define all the variables in FIRST at that point in the code.

Re: Lookup table and fill in missing values (ACS data)?

PharmlyDoc — Tue, 13 Jul 2021 15:50:06 GMT

What's your source for this? Does SAS have any references to using the update statement in this manner?

Other than this source: https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2020/5184-2020.pdf

And how do I maintain the original column order from the first dataset?

PAT_ID SEX AGE INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK

instead of

PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK SEX AGE

Re: Lookup table and fill in missing values (ACS data)?

Astounding — Tue, 13 Jul 2021 08:32:02 GMT

A small tweak to the program should take care of that:

data want;
   update second first (in=original_100k);
   by pat_id;
   if original_100k;
run;

Re: Lookup table and fill in missing values (ACS data)?

PharmlyDoc — Tue, 13 Jul 2021 03:01:07 GMT

Actually, I should have elaborated.

In reality my second data set has 250,000 patients, whereas the first dataset has only 100,000.

The update statement in the data step that you mentioned gives me a table with 250,000 patients, when I only want the 100,000

Re: Lookup table and fill in missing values (ACS data)?

Astounding — Mon, 12 Jul 2021 23:42:34 GMT

Here's the easy way. It assumes that both data sets are sorted, and that your data sets have just one observation per PAT_ID. (Mismatches are OK.)

data want;
   update second first;
   by pad_id;
run;

For each PAT_ID, it takes data from your second data set, then replaces those values with any non-missing values from the first data set. That's a little convoluted, but sounds like it's exactly what you need.

Re: Lookup table and fill in missing values (ACS data)?

PharmlyDoc — Mon, 12 Jul 2021 21:52:55 GMT

What about if I have other variables in the first dataset that aren't in the second dataset (e.g. SEX, AGE, and a dozen more) that I want to include in the want dataset?

Re: Lookup table and fill in missing values (ACS data)?

PharmlyDoc — Mon, 12 Jul 2021 21:40:22 GMT

Alright, here is the code that I used to solved my question.

PROC SQL;
    CREATE TABLE WANT AS 
        SELECT coalesce(a.PAT_ID, b.PAT_ID) as PAT_ID, coalesce(a.INCOME_BLOCK,b.INCOME_BLOCK) AS INCOME_BLOCK,
coalesce(a.EDU_BLOCK,b.EDU_BLOCK) AS EDU_BLOCK,
coalesce(a.UNEMPLOY_BLOCK,b.UNEMPLOY_BLOCK) AS UNEMPLOY_BLOCK,
coalesce(a.HOMEOWNER_BLOCK,b.HOMEOWNER_BLOCK) AS HOMEOWNER_BLOCK
            FROM first a LEFT JOIN second b
                ON a.PAT_ID = b.PAT_ID;
QUIT;

However, I'm having trouble doing the same as above but with additional variables in the first dataset such as sex and age.

data third; 
input PAT_ID : best2. SEX :$6. AGE : best2. INCOME_BLOCK : best6. EDU_BLOCK : best4. UNEMPLOY_BLOCK : best4. HOMEOWNER_BLOCK : best4. ;
datalines;
1 Female  22 45810 93.82 13.07 9.32
2 Male   37  35264 54.94 27.81 4.76
3 Male 27     .     .     .    . 
4 Male 37     .     .     .    .
5 Female 27 42346  18.62 1.87 1.29
6 Male   28 42346  41.11 4.76 10.29
7 Female  23 250001 41.11 3.02 1.29
8 Female  21 115290 93.82 27.81 1.87
9 Female  43 215493 21.65 10.29 3.60
10 Male	 35     .     .     .    .
11 Male	 30 215493 15.61 27.81 3.02
12 Male	 21 77521  18.62 7.17  9.32
13 Male	 21 215493 54.94 5.95  27.81
14 Male	 28     .     .     .    .
15 Male	 34 115290 34.35 1.29  9.32
16 Male	 38 30872  34.35 10.29 7.17
17 Female  36 105039 15.61 4.76 13.07
18 Female 23    .     .     .    .
19 Female 34 38964 9.55	 4.76  2.45
20 Female 26 38964 54.94 10.29 10.29
;

Re: Lookup table and fill in missing values (ACS data)?

mklangley — Mon, 12 Jul 2021 20:49:10 GMT

Assuming you don't have duplicate PAT_IDs in your SECOND dataset, this works, using a LEFT JOIN and COALESCE():

proc sql;
    create table want as
    select f.pat_id
          ,coalesce(f.income_block, s.income_block) as income_block
          ,coalesce(f.edu_block, s.edu_block) as edu_block
          ,coalesce(f.unemploy_block, s.unemploy_block) as unemploy_block
          ,coalesce(f.homeowner_block, s.homeowner_block) as homeowner_block
    from first f
    left join second s
        on f.pat_id = s.pat_id;
quit;

Lookup table and fill in missing values (ACS data)?

PharmlyDoc — Mon, 12 Jul 2021 20:12:00 GMT

I have two tables, first and second, that contains data from the American Community Survey (ACS). The first table has missing values (always across 4 variables), whereas the second table contains values I wish to fill in for the missing values in the first table (through matching/joining by PAT_ID).

/* ACS variables at census block level */
data first;
input PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK @@;
datalines;
1 45810 93.82 13.07 9.32
2 35264 54.94 27.81 4.76
3 . . . . 
4 . . . . 
5 42346 18.62 1.87 1.29
6 42346 41.11 4.76 10.29
7 250001 41.11 3.02 1.29
8 115290 93.82 27.81 1.87
9 215493 21.65 10.29 3.60
10 . . . . 
11 215493 15.61 27.81 3.02
12 77521 18.62 7.17 9.32
13 215493 54.94 5.95 27.81
14 . . . . 
15 115290 34.35 1.29 9.32
16 30872 34.35 10.29 7.17
17 105039 15.61 4.76 13.07
18 . . . .  
19 38964 9.55 4.76 2.45
20 38964 54.94 10.29 10.29
;

/* variables at the zipcode level (INCOME_ZIPCODE EDU_ZIPCODE UNEMPLOY_ZIPCODE HOMEOWNER_ZIPCODE)
 but named the same as above to help with merging*/ 
data second; 
input PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK @@; 
datalines; 
1 50127 8.33 35.00 82.71 
2 43578 29.01 44.06 58.68 
3 36251 14.93 50.14 62.01 
4 136686 14.93 29.44 67.86 
5 136686 22.49 33.52 87.99 
6 43578 22.49 50.14 87.99 
7 32374 10.86 33.52 64.28 
8 39755 7.51 56.55 58.68 
9 34548 12.48 56.55 80.46 
10 34548 14.93 38.84 58.68 
11 50127 72.73 31.91 82.71 
12 50127 9.22 50.14 87.99 
13 37981 14.93 44.06 75.23 
14 61270 8.33 41.37 87.99 
15 57101 8.33 50.14 80.46 
16 36251 4.79 38.84 62.01 
17 136686 8.33 48.14 80.46 
18 37981 72.73 31.91 67.86 
19 50127 9.22 33.52 62.01 
20 136686 19.81 41.37 62.01 
;

/* values filled in for missing are from zip-code level instead of census block level */

data want;

input PAT_ID INCOME_BLOCK EDU_BLOCK UNEMPLOY_BLOCK HOMEOWNER_BLOCK @@; datalines;

1 45810 93.82 13.07 9.32
2 35264 54.94 27.81 4.76
3 36251 14.93 50.14 62.01
4 136686 14.93 29.44 67.86
5 42346 18.62 1.87 1.29
6 42346 41.11 4.76 10.29
7 250001 41.11 3.02 1.29
8 115290 93.82 27.81 1.87
9 215493 21.65 10.29 3.60
10 34548 14.93 38.84 58.68
11 215493 15.61 27.81 3.02
12 77521 18.62 7.17 9.32
13 215493 54.94 5.95 27.81
14 61270 8.33 41.37 87.99
15 115290 34.35 1.29 9.32
16 30872 34.35 10.29 7.17
17 105039 15.61 4.76 13.07
18 37981 72.73 31.91 67.86
19 38964 9.55 4.76 2.45
20 38964 54.94 10.29 10.29

;

How to print only first page ODS PDF

Easybeat — Mon, 12 Jul 2021 09:48:39 GMT

Hello!
I’ve got quite big proc tabulate report on 90 pages. I need to print it out on PDF, but only the first page. How do I do it?

Renaming variable based on a correlation file and Subquerry error

Blandine — Fri, 09 Jul 2021 15:20:42 GMT

Hello, I am having a file with data fragmentation. The variable "Structure_name" (in the file WA.WA_datanewname) has 2 ID for the same bridge and I'm trying to make it 1 ID using a correlation file (fh_wa02) that was given to me. The correlation file has old_structure_number_008 and New_structure_number_008, so basically replace I want to replace the old_structure_number_008 with the New_structure_number_008 without lossing any data. But I am having 2 problems:

1- The subquery error below. I solve it by deleting that structure number which is probably not an efficient solution

ERROR: Subquery evaluated to more than one row.
NOTE: Correlation values are: Structure_Number='WN-005051656925

2. It's seemed like I am losing some bridge ID. I have 4 bridges that I checked if the code correctly replace the old_structure_number_008 with the New_structure_number_008 but the code simply deleted the old_structure_number_008 . I don't know what to do

Here is my code

proc sql;
create table WA.WA_NBI_data_corrected as
select
case when exists (select * from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)
then (select NEW_STRUCTURE_NUMBER_008 from fh.fhwa_WA02 where OLD_STRUCTURE_NUMBER_008=Structure_Number)
else Structure_Number
end as Structure_Number
, Year_Built
, ADT
, STRUCTURE_KIND_043A
, STRUCTURE_KIND_043B
, Bridge_Age
, DECK_COND_058
, SUPERSTRUCTURE_COND_059
, SUBSTRUCTURE_COND_060
, Deck_Structure_Type
, Inspection_Year
from WA.WA_datanewname;
quit;

Re: PROC IMPORT Excel hyperlink to a SAS data set

Reeza — Wed, 07 Jul 2021 20:24:18 GMT

When importing from Excel, it reads the value of the cell, not the formulas of the cell.
I'm not aware of any method to retrieving the formulas beyond parsing the XML file which is very cumbersome.

PROC IMPORT Excel hyperlink to a SAS data set

Dick_Pickett — Wed, 07 Jul 2021 20:10:48 GMT

Is this possible?

Example: An Excel cell contains: =HYPERLINK("[mailto:SNFVBP@rti.org]", "SNFVBP@rti.org")

PROC IMPORT does not import the cell contents.

PROC IMPORT datafile = "report" out = &tabnamecon
dbms = XLSX REPLACE ;

sheet = "&tabname" ;
getnames = NO ;
%IF &columnheadline = NA %THEN %DO ;
datarow = 1
%END ;
%IF &columnheadline ^= NA %THEN %DO ;
datarow=&columnheadline
%END ;
;
RUN ;

Re: Proc Report

Cynthia_sas — Wed, 07 Jul 2021 16:44:12 GMT

Hi:

PROC REPORT has a BREAK statement and an RBREAK statement. You can have the Break line (or summary line) added AFTER a group or order item or BEFORE a group or order item. Without data, no one can test or modify your code. However in a quick look, it seems to me that you need this statement to start:

BREAK BEFORE DM / SUMMARIZE;

RBREAK BEFORE/ SUMMARIZE;

BREAK AFTER DM / SUMMARIZE;

RBREAK AFTER/ SUMMARIZE;

Although I'm not sure how you want your Conversion Rate percentage treated in the sub-totals or totals. You can find examples of BREAK and RBREAK in previous forum postings and in the PROC REPORT documentation.

Cynthia

Proc Report

Eugenio211 — Wed, 07 Jul 2021 16:32:40 GMT

Hello, I wanted to add sub totals and Total to my proc report steps. I am showing this in an excel format. below is the code that I have

ods excel file="directory path\Test_List;

proc report data=Test_List;
columns dm location campaign_month, (customer_count converted Conversion_Rate);
define dm/group;
define location/group;
define campaign_month/across order=data;
define customer_count/sum 'Preapprovals';
define converted/sum 'Conversion';

run;

ods excel close;

Current Output:

		Jan 2021			Feb 2021			Mar 2021			Apr 2021			Jun 2021
DM	LOCATION	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate
DM2	1	533	31	6%	22	8	36%	503	16	3%	30	6	20%	485	6	1%
	2	352	18	5%	19	3	16%	330	15	5%	29	7	24%	337	2	1%
	3	576	18	3%	27	2	7%	529	13	2%	36	1	3%	527	3	1%
	4	430	24	6%	12	2	17%	400	18	5%	18	4	22%	372	5	1%
	5	666	32	5%	24	5	21%	612	27	4%	27	3	11%	601	5	1%
	6	473	26	5%	11	3	27%	437	18	4%	26	5	19%	436	8	2%
	7	216	13	6%	10	7	70%	191	12	6%	8	5	63%	166	3	2%
	8	377	25	7%	16	4	25%	343	15	4%	23	3	13%	346	1	0%
	9	352	25	7%	6	2	33%	307	8	3%	23	2	9%	316	5	2%
DM3	10	349	13	4%	8	3	38%	332	15	5%	9	1	11%	304	1	0%
	11	497	17	3%	14	3	21%	477	17	4%	22	2	9%	431	4	1%
	12	782	30	4%	32	6	19%	729	19	3%	51	9	18%	693	6	1%
	13	605	24	4%	19	3	16%	560	12	2%	38	3	8%	517	3	1%
	14	719	55	8%	33	5	15%	626	28	4%	47	9	19%	608	9	1%
	15	347	14	4%	8	3	38%	331	15	5%	16	4	25%	327	0	0%
	16	336	19	6%	19	3	16%	309	7	2%	34	1	3%	332	2	1%
DM4	17	204	10	5%	7	3	43%	181	5	3%	8	1	13%	169	2	1%
	18	136	6	4%	5	2	40%	132	5	4%	5	2	40%	107	0	0%
	19	110	3	3%	2	1	50%	104	3	3%	5	2	40%	80	1	1%
	20	111	1	1%	3	0	0%	106	3	3%	8	1	13%	91	0	0%
	21	86	1	1%	6	3	50%	83	2	2%	3	1	33%	78	0	0%

Desired Ouput:

		Jan 2021			Feb 2021			Mar 2021			Apr 2021			Jun 2021
DM	LOCATION	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate
DM2	SubTotal
	1	533	31	6%	22	8	36%	503	16	3%	30	6	20%	485	6	1%
	2	352	18	5%	19	3	16%	330	15	5%	29	7	24%	337	2	1%
	3	576	18	3%	27	2	7%	529	13	2%	36	1	3%	527	3	1%
	4	430	24	6%	12	2	17%	400	18	5%	18	4	22%	372	5	1%
	5	666	32	5%	24	5	21%	612	27	4%	27	3	11%	601	5	1%
	6	473	26	5%	11	3	27%	437	18	4%	26	5	19%	436	8	2%
	7	216	13	6%	10	7	70%	191	12	6%	8	5	63%	166	3	2%
	8	377	25	7%	16	4	25%	343	15	4%	23	3	13%	346	1	0%
	9	352	25	7%	6	2	33%	307	8	3%	23	2	9%	316	5	2%
DM3	SubTotal
	10	349	13	4%	8	3	38%	332	15	5%	9	1	11%	304	1	0%
	11	497	17	3%	14	3	21%	477	17	4%	22	2	9%	431	4	1%
	12	782	30	4%	32	6	19%	729	19	3%	51	9	18%	693	6	1%
	13	605	24	4%	19	3	16%	560	12	2%	38	3	8%	517	3	1%
	14	719	55	8%	33	5	15%	626	28	4%	47	9	19%	608	9	1%
	15	347	14	4%	8	3	38%	331	15	5%	16	4	25%	327	0	0%
	16	336	19	6%	19	3	16%	309	7	2%	34	1	3%	332	2	1%
DM4	SubTotal
	17	204	10	5%	7	3	43%	181	5	3%	8	1	13%	169	2	1%
	18	136	6	4%	5	2	40%	132	5	4%	5	2	40%	107	0	0%
	19	110	3	3%	2	1	50%	104	3	3%	5	2	40%	80	1	1%
	20	111	1	1%	3	0	0%	106	3	3%	8	1	13%	91	0	0%
	21	86	1	1%	6	3	50%	83	2	2%	3	1	33%	78	0	0%

Thanks a lot.

Re: What would be the numeric limit for percentage in SAS?

ballardw — Wed, 07 Jul 2021 14:33:26 GMT

wrote:

I was testing SAS numeric limit for percentage calculation.

For this, it would be Num8. with format 10.8. Is there a better way to define a SAS column for percentage instead of defining format 10.8 with Num8. especially for cases like 33.333333?

Depends on what you want to see. Consider one format:

data example;
   x=1/3;
   put x=best3.;
   put x=best4.;
   put x=best5.;
   put x=best6.;
   put x=best7.;
   put x=best8.;
   put x=best9.;
   put x=best10.;
   put x=best11.;
   put x=best12.;
   put x=best13.;
   put x=best14.;
   put x=best15.;
   put x=best16.;
   put x=best17.;
   put x=best18.;
   put x=best19.;
   put x=best20.;
run;

Look in the Log if you aren't familiar with the Put function.

Pick a format, or make one of your own, that displays things the way you want.

Re: how do I use if/and/delete statement to delete sex-dependent values

ayosas — Tue, 06 Jul 2021 23:56:29 GMT

Thank you!! This worked, much appreciation

Re: how do I use if/and/delete statement to delete sex-dependent values

Reeza — Tue, 06 Jul 2021 23:44:29 GMT

You're only deleting records where BOTH are missing, you want to delete records where either are missing and use OR instead of AND.

if IMQ070=. or IMQ060=. then delete;

However, since you can only be one, I don't think that's what you want either as that will delete all record essentially since the other one will always be the opposite.

I would suggest creating a new variable HPV vaccine:

HPV_Vaxx = coalesce(IMQ070, IMQ060);
if missing(IMQ070) then gender=0; else gender=1;

how do I use if/and/delete statement to delete sex-dependent values

ayosas — Tue, 06 Jul 2021 23:15:31 GMT

set ile.nhanes2016 (keep= SMQ040 IMQ060 IMQ070 IMQ100 RIAGENDR 
RIDAGEYR DMDCITZN DMDMARTL BMXBMI DMDEDUC2 INDHHIN2
MCQ220);
if IMQ070=. and IMQ060=. then delete;
run;

proc freq data=ile.nhanes2016;
tables IMQ060 IMQ070 / nocum missing;
run;

IMQ070 value is for males and IMQ060 is for females. Subjects have a value for one and not the other. I want to delete missing values. After running the code missing values remain for both males and females, is there something wrong with my code?

Re: What would be the numeric limit for percentage in SAS?

Tom — Sun, 04 Jul 2021 03:57:20 GMT

Not sure what your question is. SAS stores all numbers of 64 bit floating point binary. You can have up to about 15 decimal digits of precision with such numbers. Are you asking how many characters they should use when transferring the data as text string?

Re: What would be the numeric limit for percentage in SAS?

Patrick — Sun, 04 Jul 2021 03:44:18 GMT

SAS uses 8 Bytes to store numbers. This allows to store 15 digits with full precision (and up to a point also numbers with 16 digits).

SAS Formats only affect how numbers get printed. SAS will use the internally stored value for any calculation (=full precision).

Have a look at below. May-be that's going to explain to you that the problem you're raising doesn't really exist.

data demo;
  do var1=0.105, 1/3, 0.5, 1.6, 1;
    var2=var1;
    var3=var1;
    var4=var1;
    output;
  end;
  format var2 best32. var3 percent16. var4 percent16.2;
  stop;
run;

proc print data=demo;
run;

What would be the numeric limit for percentage in SAS?

StickyRoll — Sun, 04 Jul 2021 03:22:28 GMT

I was testing SAS numeric limit for percentage calculation.

For example,

40% is 0.4 in SAS Dataset
100% is 1 in SAS Dataset
33.333333% is 0.3333333 in SAS Dataset

Another Database team is going to populate percentage data for me. Now the question is, I tried testing in BASE SAS and I found out SAS can support up to 8 decimal places as followed.

0.12345678

There might be cases where by 100 divides by 3 will get 33.3333333.

For this, it would be Num8. with format 10.8. Is there a better way to define a SAS column for percentage instead of defining format 10.8 with Num8. especially for cases like 33.333333?

Re: Reporting summarised data with Proc Tabulate

ChrisNZ — Sat, 03 Jul 2021 02:30:40 GMT

The only way is to pre-summarise and then set the values in a data step using the logic you describe.

Then you display them as you do now.

Re: Reporting summarised data with Proc Tabulate

BruceBrad — Fri, 02 Jul 2021 07:41:55 GMT

One way to summarise: If the variance is positive, show missing, if the variance is zero, show the mean (or the first, last - all the same).

Re: Reporting summarised data with Proc Tabulate

ChrisNZ — Fri, 02 Jul 2021 07:16:18 GMT

what would the cell display?

Re: Reporting summarised data with Proc Tabulate

BruceBrad — Fri, 02 Jul 2021 07:08:11 GMT

Doesn't always work for data structure I'm using. Eg often I will have a table where some cells are constants (and I'd like to double-check this) while others are variable and I want to calculate the mean. It would be neat if I could write a cell function for this.

Re: Reporting summarised data with Proc Tabulate

ChrisNZ — Fri, 02 Jul 2021 06:40:14 GMT

Your constraints are unclear.

Can you run proc tabulate twice? Once with statistics N and option OUT=, to check the underlying data, and then for the report?

proc report is typically more flexible than proc tabulate, not less.

Re: How to load excel with multiple sheets dynamically into SAS datset?

StickyRoll — Thu, 01 Jul 2021 12:49:02 GMT

Spot on.

I tried to delete the excel and create a dummy excel. Using the script provided by worked like a charm with the new excel.

Thanks for the help. Appreciate it.

Re: How to load excel with multiple sheets dynamically into SAS datset?

Tom — Thu, 01 Jul 2021 12:48:00 GMT

Most likely the path you have for the filename does not exists. SAS does not know that you intend to READ from that new XL libref. You might be intending to create a new XLSX file so that you can WRITE to it.

Make sure the file exists.

data _null_;
  filename = "D:\SASexcel.xlsx"; 
  if fileexist(filename) then put filename=:$quote. 'exists.';
  else put filename=:$quote. 'does NOT exist.';
run;

Re: How to load excel with multiple sheets dynamically into SAS datset?

PaigeMiller — Thu, 01 Jul 2021 12:46:40 GMT

Does the file D:\SASexcel.xlsx actually exist? The message you are getting is the same message I get when I use a LIBNAME that points to an Excel file that doesn't exist.

Re: How to load excel with multiple sheets dynamically into SAS datset?

StickyRoll — Thu, 01 Jul 2021 12:40:05 GMT

Hi there,

I have this warning:

28         libname xl XLSX "D:\SASexcel.xlsx"; 
NOTE: Libref XL was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: D:\SASexcel.xlsx
29         
30         options validvarname=V7;


31         proc copy in=xl out=work memtype=data;
32         run;

WARNING: Input library XL is empty.

My script as below.

libname xl XLSX "D:\SASexcel.xlsx"; 

options validvarname=V7;
proc copy in=xl out=work memtype=data;
run;
quit;

I think SAS couldn't read my excel sheets/tabs. Not sure what caused that. Did you see anything wrong with my script?

Re: How to load excel with multiple sheets dynamically into SAS datset?

PaigeMiller — Thu, 01 Jul 2021 11:54:42 GMT

Here is my modification that works for me, and also allows sheet names and variable names to have special characters or blanks.

libname xl XLSX "D:\Excel.xlsx";

options validvarname=v7;

proc copy in=xl out=work memtype=data;
run;
quit;

By the way, may I make a request ? From now on please paste the actual code (or better yet, paste the log so we can see the code and the ERRORs, WARNINGs and NOTEs) into your message. The code you showed cannot possibly work, because your LIBNAME statement is wrong, and is missing a double quote after the .xlsx, and that could be the cause of the error you are seeing.

Re: How to load excel with multiple sheets dynamically into SAS datset?

StickyRoll — Thu, 01 Jul 2021 11:04:34 GMT

here is my code:

libname xl XLSX "D:\Excel.xlsx;


proc contents data=xl._ALL_;

RUN;

I get a warning

"no matching members in directory".

However, the sample in the tutorial shows 3 members which I believe is the sheets.

Why is this happening? My excel has 3 sheets as well.

Re: How to load excel with multiple sheets dynamically into SAS datset?

StickyRoll — Thu, 01 Jul 2021 10:55:53 GMT

I dont see how this tutorial show us how SAS dynamically read all excel sheets/tabs automatically. I understand that I can use libname XLSX engine to read excel.

I have an excel workbook that contains 5 sheets/tabs. I don't want to hardcode the sheets/tabs in any part of the SAS script. What I want is to automatically load in all excel sheets as tables.

If I have 5 sheets, I want to create 5 datasets, all without hardcode the actual name of the excel sheets.

Re: How to load excel with multiple sheets dynamically into SAS datset?

Patrick — Thu, 01 Jul 2021 10:21:03 GMT

A libname using the xlsx engine will allow you to access the sheets directly like SAS tables under a single libref. Example here in one of Chris Hemedinger's fantastic blogs.

You then can just use a proc datasets/copy to copy all these Excel sheets to another libref which uses the SAS engine - and they will "magically" become SAS tables.

Not sure how you intend to deal with such "random" tables - is it only the names or will also the table structures differ - but you don't provide the information to make any further statements.

How to load excel with multiple sheets dynamically into SAS datset?

StickyRoll — Thu, 01 Jul 2021 09:55:42 GMT

I have an excel workbook which contains random sheets every month.

As such, I want to have a script to load this excel workbook without hardcode any of the worksheet name inside the script. Is there a way in SAS where I can get the list of all sheets in the excel?

This way, I can assign the list to macro and easily store in SAS.

Re: Importing SPSS weighted data

kristenl — Wed, 30 Jun 2021 21:13:56 GMT

Great, thanks.

Re: Importing SPSS weighted data

Tom — Wed, 30 Jun 2021 20:52:52 GMT

It seems to me that SPSS syntax for specifying what variable to use for weighting is very similar to the SAS syntax.

https://libguides.library.kent.edu/SPSS/WeightCases

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.3/statug/statug_freq_syntax13.htm

Re: Importing SPSS weighted data

kristenl — Wed, 30 Jun 2021 19:41:36 GMT

Thanks very much Reeza, this makes sense.

Re: Importing SPSS weighted data

kristenl — Wed, 30 Jun 2021 19:41:05 GMT

Yes, that's correct - SPSS file had 158,000, whereas SAS had 7590.
However, Reeza is suggesting that weighted data in SPSS is not altered, which makes sense considering the small sample in SAS.

Re: Importing SPSS weighted data

PaigeMiller — Wed, 30 Jun 2021 19:39:11 GMT

wrote:

Thanks so much for your reply. My (edited) SAS syntax is below. In the SPSS file, I have applied weights. The weighted sample is 158,000; however, after importing it into SAS, I only have the original (non-weighted) sample of 7590.

Do you mean that the SPSS file had 158000 records, while the SAS file has 7590 records? What does "weighted sample" mean with respect to a data set? If there is a weight variable, what happens if you add up all the weights?

Re: Importing SPSS weighted data

Reeza — Wed, 30 Jun 2021 19:35:50 GMT

Weighted data typically just means you have a column(s) of weights for your data. It doesn't affect the number of observations. Any proc using the data will have a WEIGHT statement where you provide the weight variable and then the analysis will factor in the weights.

It does not change your input data or data structure in any fashion.

wrote:

HI,

Just wondering if someone can help. I'm trying to import weighted data from SPSS into SAS. The file imports, however, the weighted data does not import properly and the original sample size remains. I've tried to find this info in user manuals, but can't seem to find anything. Any ideas what I'm doing wrong?

thanks

Re: Importing SPSS weighted data

kristenl — Wed, 30 Jun 2021 19:35:41 GMT

Thanks so much for your reply. My (edited) SAS syntax is below. In the SPSS file, I have applied weights. The weighted sample is 158,000; however, after importing it into SAS, I only have the original (non-weighted) sample of 7590.

PROC IMPORT OUT=OIS.finalweighted
DATAFILE="C:\Users\...NEW_weighted.sav"
DBMS=SPSS REPLACE;
RUN;

Re: Importing SPSS weighted data

PaigeMiller — Wed, 30 Jun 2021 19:28:35 GMT

The file imports, however, the weighted data does not import properly and the original sample size remains.

As far as I know, the data should not change when importing from SPSS to SAS, so I don't know what the above means. Can you explain this further? Can you give a small example?

Also, how are you doing this "import"?

Importing SPSS weighted data

kristenl — Wed, 30 Jun 2021 19:22:50 GMT

HI,

Just wondering if someone can help. I'm trying to import weighted data from SPSS into SAS. The file imports, however, the weighted data does not import properly and the original sample size remains. I've tried to find this info in user manuals, but can't seem to find anything. Any ideas what I'm doing wrong?

thanks

Re: Reporting summarised data with Proc Tabulate

BruceBrad — Mon, 28 Jun 2021 08:25:00 GMT

Bumping this. It's 7 years later and I would still like to be able to do this! Any progress on using custom functions in proc tabulate?

Re: Join datasets by group in sql

Sajid01 — Sun, 27 Jun 2021 17:52:28 GMT

I suggest you stick with data step merge (in comparison to proc SQL).

The already have a code that needs some modifications as follows and you will be nearing your goal.
As suggested by @ballardw make corrections to the sort syntax.

In addition rename the common variables (other than by variables) in one of the datasets.

You may have to do home post processing to the merged dataset to get exactly what you want,

but you can easily achieve your objective of purging the rows of dataset2 from dataset1.

Re: Average Square Error way to high ??

PaigeMiller — Sun, 27 Jun 2021 18:32:50 GMT

I faced an issue of skewness with my continuous variables ...

Are you talking about skewness of the target (or response) variable? Or skewness of the predictor variables?

... for which I firstly transformed into log but it didn't work

What didn't work? What did you see that makes you think it didn't work? What variable(s) did you transform?

but I got validation average square error as 116 so I am confused how to interpret such huge error

Did you transform the target variable? You really haven't said if you did or did not.

What makes you think that a 116 is "huge"? What are typical values of sales? Maybe the interpretation is that the model didn't fit well; or maybe the interpretation is that you have very large values of sales and a 116 is reasonable. We need a lot more information and context here.

Re: ERROR WEB SERVICES

Sajid01 — Sun, 27 Jun 2021 17:33:01 GMT

Hello
The error message is clear, your application is not able to parse (understand) the response.

com.sas.analytics.ph.RTDMException: Error parsing webservice response from

Try sending a request from curl ( or any other client) and see the output.

Re: Average Square Error way to high ??

Sajid01 — Sun, 27 Jun 2021 17:22:19 GMT

You have squared your continuous variable. That may be the cause.

Re: Creating macro vars using PROC SQL select into

Reeza — Fri, 25 Jun 2021 18:09:28 GMT

No - run the code. It generates the query below, which is also run and the results are displayed.

select * from sashelp.class where name in (<list of names of those who are 14>);

If both are in the same system though you can just do the following - no need for formats or macro variables.

proc sql;
create table want as
select * from tableA where name in (select name from TableB);
quit;

So no need to generate a macro list or format or any code.

Re: Creating macro vars using PROC SQL select into

ballardw — Fri, 25 Jun 2021 18:02:33 GMT

I suspect a different approach such as a format might be more flexible.

Skeleton program example.

/* IF this set actually only contains valid values
   you would need a separate Fmtname value for each specific list
   of valid values. The C is for character variables, use N for numeric
*/
proc sql ;
   Create table validcntlin as
   select distinct (value) as start, "validvalue" as fmtname, label='Valid', type='C'
   from myfile;
quit;

proc format cntlin=validcntlin;
run;

data want;
   set have;
   where put(variablename,validvalue.) ne 'Valid';
run;

Advantage: once you have the cntlin data set created you don't need to pull values from the source again like you would with macro variables. Once the format is created if stored in library in the format search path you don't need to do anything until you have to update the format definition because of new data. If you need to add a new format for new variables you only need to create the data/code for that one format as the format catalog can hold many formats.

If multiple variables share the same range of valid values then you only need one format to test.

If variables are numeric and continuous it would likely be easier to write range statements in proc format, especially if you have a few code values .

Proc format;
value somefmtname
1 - 3000='Valid'
9999='Valid'
;
run;

For an example that only reports numbers in the range of 1 to 3000 (including decimal portions) and the code 9999 as 'Valid'.

This particular approach would have a formatted value of the numeric value as a BEST for anything except those in the ranges. OR use an othere= 'Invalid' and test for 'Invalid' as the formatted value. It is possible to code an Other into a Cntlin data set but would need more coding to add to the cntlin created in SQL to append a record with the variable, HLO and the value needed to set that.

Personally I've never used a discrete format with more than about 2,000 values but I don't see a problem. And the use is dead easy and possible in lots of places.

Obviously that dummy Want set should be restricted to only include identification variables and the invalid value variable(s).

I actually use a few formats like this when I read data sets because I have data sources that slip in new things like site identifiers.

So I have in my data step that reads the data lines of code like:

If put (siteid, validsitefmt.) ne 'Valid' then put "WARNING: Invalid site id of " siteid +1 "for " idvariable= _n_= ;

Then my log shows information about the problems before the data gets processed any further and I can 1) validate whether that value should be there from the sources and take appropriate action. Sometimes it is a simple spelling error, sometimes in means a new site and I update the metadata about the sites and the validation format (append a record if the Cntlin is a data set, or add line of code to Proc Format statements) and rerun the proc format to update. Then reread the file.

Re: Creating macro vars using PROC SQL select into

Walternate — Fri, 25 Jun 2021 17:55:06 GMT

So this example creates a test dataset which consists of each valid value listed out separated by commas?

Re: Creating macro vars using PROC SQL select into

Reeza — Fri, 25 Jun 2021 17:35:54 GMT

Will a subquery work instead?

Otherwise a data step that generates the full code dynamically does not have that limit, ie CALL EXECUTE()/DOSUBL.

%let age = 14;

data test;
set sashelp.class end = eof;;
where age = &age.;

if _n_ =1 then do;
	call execute('proc sql; select * from sashelp.class where name in (');
end;

if not eof then do;
call execute(quote(name));
call execute(",");
end;

if eof then do;
call execute(quote(name));
call execute("); quit;");
end;

run;

wrote:

Hi all,

I have a list of tens of thousands of valid values for a set of variables and am trying to set up a lookup to determine which records have invalid values for these vars. I am trying to use PROC SQL select into to create a macro list of valid values for the variable:

proc sql noprint;

select(value) into :list_of_values separated by ','

from myfile;

quit;

With this in place, I can just set up a lookup for whether the value of a given variable matches one of the values in &list_of_values.

The problem is that I have too many values listed so SAS is indicating that the &list_of_values macro variable cannot be built because it would be too long. Is there a workaround for this? The only thing I can think to do is break up the variable list into multiple macro lists, but that is a bit of a pain.

Re: Creating macro vars using PROC SQL select into

PaigeMiller — Fri, 25 Jun 2021 17:27:45 GMT

If macro variables can't contain the desired information because it exceeds the maximum length of a macro variable, another solution is CALL EXECUTE.

But either with macros or CALL EXECUTE, how do you indicate to the program which values are valid and which are invalid?

Creating macro vars using PROC SQL select into

Walternate — Fri, 25 Jun 2021 17:24:00 GMT

Hi all,

I have a list of tens of thousands of valid values for a set of variables and am trying to set up a lookup to determine which records have invalid values for these vars. I am trying to use PROC SQL select into to create a macro list of valid values for the variable:

proc sql noprint;

select(value) into :list_of_values separated by ','

from myfile;

quit;

With this in place, I can just set up a lookup for whether the value of a given variable matches one of the values in &list_of_values.

The problem is that I have too many values listed so SAS is indicating that the &list_of_values macro variable cannot be built because it would be too long. Is there a workaround for this? The only thing I can think to do is break up the variable list into multiple macro lists, but that is a bit of a pain.

Re: SAS Access to Snowflake

SASKiwi — Thu, 24 Jun 2021 21:28:28 GMT

Great, its the same thing. Please mark your post as answered then.

Re: SAS Access to Snowflake

garyhaas — Thu, 24 Jun 2021 21:19:12 GMT

Yes, that was the advice of Tech support and it works. I used PROC APPEND rather than PROC DATASETS but that shouldn't matter.

Thank you for the advice!

Re: SAS Access to Snowflake

SASKiwi — Thu, 24 Jun 2021 21:10:20 GMT

Just to clarify, are you just appending rows here? If so I suggest you try PROC DATASETS with the APPEND statement where the BASE table is pre-defined table and the APPEND table is your SAS table.

Re: SAS Access to Snowflake

garyhaas — Thu, 24 Jun 2021 16:05:14 GMT

Need to pre-define table with Create table statement, not delete it before load

219 data mydblib.zipterr_rbu_&qtr
220 (bulkload=yes
221 bl_internal_stage="user/someuser"
222 BL_COMPRESS=yes);
223 set idata.rbu_&terr._zip
224 ;

SNOWFLAKE: AUTOCOMMIT turned ON for connection id 1

SNOWFLAKE_1: Prepared: on connection 1
SELECT * FROM "CDW_US_ASB_GBA_DB"."zipterr_rbu_Q221b" WHERE 0=1

DBMS_TIMER: summary statistics
DBMS_TIMER: total SQL prepare seconds were: 0
DBMS_TIMER: dbiopen/dbiclose timespan was 0.
ERROR: The SNOWFLAKE table zipterr_rbu_Q221b has been opened for OUTPUT. This table already exists, or there is a name
conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE
option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode.
This prevents execution of subsequent data modification statements.
NOTE: DATA statement used (Total process time):
real time 2.62 seconds
cpu time 0.75 seconds

Re: SAS Access to Snowflake

SASKiwi — Tue, 22 Jun 2021 23:39:13 GMT

Can you please post the SAS log including code and notes?

SAS Access to Snowflake

garyhaas — Tue, 22 Jun 2021 22:58:42 GMT

Want to use Bulkload to an existing Snowflake table that has been created using a Create table statement. But libname bulk load complains that table already exists. Is there a way to use bulkload using a connect statement with execute commands?

Re: Diference Date (MONTH) by ID

Andres_Fuentes1 — Tue, 22 Jun 2021 20:08:20 GMT

Nice, Thanks you.

Re: Diference Date (MONTH) by ID

Jerrya00 — Tue, 22 Jun 2021 19:25:41 GMT

Please try this:
proc sort data=your_data; by ID date;
run;

data results;
set your_data;
by ID date;;
retain pre_Date .;
if first.ID then do;
n_month=.;
pre_Date=date;
end;
else do;
n_month=intck('month', pre_Date, date, 'C');
pre_Date=date;
end;
drop pre_Date;
run;

Re: Diference Date (MONTH) by ID

Reeza — Tue, 22 Jun 2021 17:48:15 GMT

That's Excel, not SAS data shown. In the future please show the SAS version. I'm assuming you have your data imported and that your date are SAS dates, eg numeric with a date format.

proc sort data=have;
by ID;
run;

data want;
set have;

by ID;

prev_date = lag(date);

if not first.id then n_month = intck('month', date, prev_date, 'C');

run;

You may need to play with the INTCK() function to get the exact numbers you want but this gives you an idea of how to do it.

wrote:

Hello good morning, I could not find the way to do the following, I have an ID and a date of a certain event. I must add the difference in months of an event with the following one by ID.

Example in excel in the photograph.

someone who can help me, Greetings.

Re: Diference Date (MONTH) by ID

PaigeMiller — Tue, 22 Jun 2021 18:05:30 GMT

And so what is the desired result? Do you want 9 for ID = 1 and 12 for ID = 2 and 12 for ID=3?

Is your date variable a character variable in SAS or a numeric variable in SAS? What does PROC CONTENTS say?

FYI, please do not provide data as screen captures from Excel. The preferred method to provide data is:

https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Diference Date (MONTH) by ID

Andres_Fuentes1 — Tue, 22 Jun 2021 17:19:34 GMT

Hello good morning, I could not find the way to do the following, I have an ID and a date of a certain event. I must add the difference in months of an event with the following one by ID.

Example in excel in the photograph.

someone who can help me, Greetings.

Re: Defining a variable within a date interval

Ksharp — Sun, 20 Jun 2021 11:52:01 GMT

Assuming there were no tie for DATE .

data have;
  infile datalines truncover;
  input Location $ Date:mmddyy10. N T;
  format date date9.;
  datalines;
A 06/19/2020 3 2
A 06/29/2020 5 3
A 06/30/2020 1 1
A 7/01/2020 4 3
A 7/05/2020 7 1
A 07/15/2020 2 0
A 07/19/2020 3 1
A 07/21/2020 0 0
A 07/22/2020 0 0
A 08/01/2020 0 0
A 08/05/2020 0 0
A 08/15/2020 0 0
B 5/12/2020 4 4
B 05/24/2020 1 0
B 05/27/2020 3 2
;

data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h();
  h.definekey('date');
  h.definedata('N','T');
  h.definedone();
 end;
set have;
by Location;
if first.Location then h.clear();
h.add();
sum_n=0;sum_t=0;
do i=date-1 to intnx('month',date,-1,'s') by -1;
  if h.find(key:i)=0 then do;sum_n+n;sum_t+t;end;
end;
want=divide(sum_t,sum_n);
drop i sum_: ;
run;

Re: Defining a variable within a date interval

PaigeMiller — Sun, 20 Jun 2021 11:45:49 GMT

A PROC SUMMARY solution (because PROC SUMMARY is my favorite PROC 😁 ) with a MULTILABEL format. I didn't do the division at the end, but you can add that in yourself.

data have0;
infile cards delimiter='|' truncover;
input Location $ Date :mmddyy10. Nbr_warnings_N Nbr_correct_warnings_T;
cards;
A|06/19/2020|3|2
A|06/29/2020|5|3
A|06/30/2020|1|1
A|07/01/2020|4|3
A|07/05/2020|7|1
A|07/15/2020|2|0
A|07/19/2020|3|1
B|05/12/2020|4|4
B|05/24/2020|1|0
B|05/27/2020|3|2
;
data _null_;
    set have0 end=eof;
    if _n_=1 then call execute('proc format; value df (multilabel)');
    call execute(cats(date-31,'-',date-1,'=',date));
    if eof then call execute ('; run;');
run;
proc summary data=have0 nway;
    class location;
    class date / mlf;
    var nbr_warnings_n nbr_correct_warnings_t;
    output out=_sums_ sum=/autoname;
    format date df.;
run;
data _sums1_;
    set _sums_;
    ndate=input(left(date),7.);
    format ndate mmddyy10.;
run;