art297 Tracker

Re: Dummy SAS project for learning.

art297 — Tue, 26 Nov 2024 18:24:30 GMT

I noticed that the link to the presentation was broken. Thanks to Chris Hemedinger I was able to find the original pdf on the Wayback Machine. Here is the link it provided: Link to my MWSUG 2013 expert panel solution presentation

Re: How to "Split Data" (By Group Processing)

art297 — Tue, 30 Apr 2024 14:24:03 GMT

KSharp, Long time no see or hear. I also have not seen or been in contact with Peter for some years now. If you want, you could always try to reach him via his LinkedIn page, namely:https://www.linkedin.com/in/petereberhardt/?originalSubdomain=ca

Re: How to "Split Data" (By Group Processing)

art297 — Mon, 29 Apr 2024 12:20:33 GMT

Paul, Nice to hear from you but, while my name is attached to it, I don't think I wrote the post to which you were replying. I never got into incorporating hash into my code like you, Don, Richard and Kilovolt did. However, as I mentioned in response to another post in this thread, I haven't used SAS in years and am simply enjoying retirement. On a different note, I think that Montreal was the first global forum I ever attended. You bring back a bunch of memories.

Re: How to "Split Data" (By Group Processing)

art297 — Mon, 29 Apr 2024 11:57:03 GMT

Sorry, I haven't used SAS in years and I no longer own the domain (or company) AnalystFinder.com. In fact, I don't even recall participating in the post that you're asking about. It looks like someone else used my logon to participate. However, at my age, memory is no longer as good as it used to be, so I may have participated and simply forgotten.

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

art297 — Thu, 08 Oct 2020 15:49:51 GMT

I don't think the macro will work with Enterprise Guide. If you have access to a copy of SAS, on either a Windows-based machine or Windows-based server, the macro would do what you want. It's intended to submit code, like your proc export code, but that uses vb script to (1) copy and paste your data to a range that you specify (i.e., not a predefined range); (2) not alter existing formats; and/or (3) use an existing workbook or template as a template to paste data into a range.

Yes, it can be run as a totally hands-off approach.

Re: Exporting from SAS to Excel Worksheet without overwriting formatting and tables

art297 — Thu, 08 Oct 2020 15:14:07 GMT

If you're on Windows then the following macro might be what you need:

https://github.com/FriedEgg/Papers/tree/master/Excelling_to_Another_Level_with_SAS/doc

HTH,

Art

Re: Using FOR LOOPS to query a dataset multiple times by date

art297 — Mon, 05 Oct 2020 23:25:29 GMT

If you're going to do what you asked, take a look at the hash method as shown in the following presentation: https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/TASS/Tabachneck-SASTips-2011.pdf

HTH,

Art

Re: Transposing blocks of observations

art297 — Fri, 02 Oct 2020 19:33:00 GMT

@MiraKr_ ,

You could do this using proc transpose, but I find the transpose macro makes it easier when you are transposing more than one variable.

The following code will accomplish what you want:

data need;
  set have;
  if variable='INTERCEPT' then order=1;
  else if variable eq 'PRIOR_CSA' then order=2;
  else if variable eq 'MALE' then order=3;
  else if variable eq 'EASI0141' then order=4;
  else if variable eq 'AAGE' then order=5;
  else if variable eq 'EASI0142' then order=6;
run;

proc sort data=need;
  by order step;
run;

filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;

%transpose(data=need, out=want, by=variable, id=step,
  delimiter=_, var=Estimate Odd_Ratio)

HTH,

Art

Re: How to export the data to the Excel range

art297 — Fri, 25 Sep 2020 17:21:08 GMT

I was apparently wrong about that dbms=xlsx can use a range option. I was also wrong about the semi-colon, as I was actually thinking exclamation point, but that wouldn't have helped. Other than getting the macro to work, or DDE or writing your own vb code, I can only think that you might be able to use ODS and proc report. However, I can't be of much/any help there.

Re: How to export the data to the Excel range

art297 — Fri, 25 Sep 2020 16:35:56 GMT

Try with the same code, but

1) remove the sheet option and

2) add a semicolon, after the sheet name, in the range option

Re: How to export the data to the Excel range

art297 — Fri, 25 Sep 2020 15:40:44 GMT

You can use range= with proc export if you use dbms=xlsx. However, as I recall, that will allow you to write to a range but not an existing range. I.e., you can't modify an existing worksheet that way.

I did notice a possible difference between your running proc export and running the macro. With proc export you enclosed the outfile with double quotes. You don't appear to have done that with the macro. Definitely worth a try .. i.e., enclosing the name supplied to the outfile parameter with double quotes.

You could always try a dde approach but that, of course, comes with all of the problems that has caused everyone to stop using dde.

Re: How to export the data to the Excel range

art297 — Fri, 25 Sep 2020 12:17:30 GMT

A first step would be to find out where you can actually create a file. Can you use proc export to create an Excel file of sashelp.class? If so, what specifically did you use as the path and name of the file?

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 17:54:32 GMT

I suggest you check with your IT department. They can configure your environment so that the server will recognize c: (or some other drive) that exists on your computer and which you can write to.

Once that is done, you should be able to use the macro.

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 17:17:47 GMT

In your call to the macro you are setting the outfile to be:

c:\temp\class.xlsx

Does your server know what c:\temp is? I don't think so because it is trying to write to:

"',"\\laxxxx.com\LMPAPROD\USINE_PRIIPS\5. RETRAITEMENTS MANUELS\Chaine

Profils\4. Analyse\Marie_TEST\class1.xlsx",'"

As an outfile the macro needs to be supplied with a file that it can create.

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 13:49:11 GMT

In order to know what is happening, I'd need to know what is written to your log. Do you know why you're not getting anything written to your log?

Out of curiosity, what version of SAS are you running?

Re: How to export the data to the Excel range

art297 — Thu, 24 Sep 2020 13:06:17 GMT

Out of curiosity, have you downloaded and compiled the macro before trying to use it?

Does your log provide any feedback?

Re: How to export the data to the Excel range

art297 — Wed, 23 Sep 2020 17:19:06 GMT

Try running the macro but including the following parameter and value:

usenotepad=Y

Often, if running on a server, the server doesn't have direct access to your system's clipbrd. As such, the above mentioned parameter will use the server's copy of Notepad to mimic a copy/paste operation.

Let me/us know if that worked.

Re: How to export the data to the Excel range

art297 — Wed, 23 Sep 2020 16:48:04 GMT

What operating system is the server running?

Re: Help using SASHELP.ZIPCODE

art297 — Tue, 22 Sep 2020 19:37:37 GMT

I'm not sure how the zipcode file is actually sorted, so I added a recnum to each record and used it to eliminate all but the last record for a given country within a state.

However, the following will only work as intended if your state and country abbreviations/names are the same as those in the sashelp.zipcode file. That won't be the case for Rifle, CO as there is no such country in the sashelp.zipcode file.

data have;
  informat County $32. State $2.;
  input ID  County State;
  cards;
1  Denver     CO 
2  Jefferson  CO
3  Rifle      CO
;
run;

data county_zipcode;
  set sashelp.zipcode (keep=zip statecode countynm
                       rename=(statecode=state
                               countynm=county
                               zip=zip_code));
  recnum=_n_;
run;
  
proc sort data=county_zipcode;
  by state county descending recnum;
run;

proc sort data=county_zipcode nodupkey;
  by state county;
run;

data want;
  merge have(in=ina) county_zipcode (drop=recnum);
  by state county;
  if ina;
run;

Art

A better way to FLIP (i.e, transpose/make wide) a dataset

art297 — Fri, 18 Sep 2020 18:39:27 GMT

This article is a little late as it re-presents a paper that was published, in 2013, by @Ksharp, @Astounding, Joe Whitehurst and me. However, the macro presented was just updated yesterday, and the code can no longer be updated on sasCommunity.org.