RW9 Tracker

Re: How to import excel with guessingrow function

RW9 — Wed, 03 Jul 2024 16:37:50 GMT

The answer to your question lies in the post you have made:

Warning: Apparent symbolic reference TEMP not resolved.

This means the macro variable TEMP is not being created.

Thus something is amis with the code (perhaps you are running on another OS or in a system where the environment variable does not exist):

data _null_;
temp = sysget('temp'); * Put the environment variable %temp% in SAS \ variable temp
call symput ('temp', right(trim(temp))); * put the content of temp in the SAS macrovariable &temp;
run;

However, more importantly, this thread is 3 years old. Start a new thread with your question, inputs, outputs, test data etc.

I would advise against the use of VBS script as it may not work with your system. Are you trying to read in an Excel file? No information to work with, hence why posting a full question would be betteras we have no idea what you are doing or how.

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

RW9 — Wed, 28 Jun 2023 08:07:32 GMT

Hi, do bear in mind this topic was closed 6 years ago. Its a good idea to open new questions as things will have changed in that time.

In terms of will the data be the same when imported back, the answer is it will retain the data part, however anything SAS specific will not, so if you export:

A B C

1 2 3

You will get a dataset with columns named A,B,C, and one row of data 1,2,3, but you will not necessarily get the right length, format, informat. And also depending on language settings/special characters etc. the data might be different too.

Re: Deleting rows in Excel with SAS

RW9 — Fri, 21 Apr 2023 19:02:59 GMT

The functionality we are discussing is in the Subject of the question, "Deleting rows in Excel", which is why I suggest ods is not useful.

I have never, ever recommended to use DDE?

Excel's front end far exceeds most other fronts ends (Forms, text, etc.), drag and drop, formula fill, filtering, functions etc. make it very quick and easy. This of course comes at the cost of anyone who uses anything other than the front end. Hence why I don't recommend to use it as data storage.

Re: PROC SGPLOT one command to produce graph for each case? And, question about "inset" po

RW9 — Fri, 21 Apr 2023 15:34:36 GMT

Well, you could look at doing

by case;

With case being a variable which splits the graph.

But your second question may not work then. Behind the scenes sgplot and others create graph template language, you can see this by updating your code:

PROC SGPLOT DATA = filename tmplout="c:\gtl_code.txt";

The file c:\gtl_code.txt will show you what GTL is created by your sgplot. I mention this as it gives a fair bit more freedom, for instance you can have dynamic variables and all kinds of conditionals.

In terms of your specific part, I would highly recommend reading through the thousands of examples in:

https://blogs.sas.com/content/graphicallyspeaking/

Its the best resource for all graphing, with examples in sgplot, gtl. You will likely find something similar to what you want and can start with that.

Re: Deleting rows in Excel with SAS

RW9 — Fri, 21 Apr 2023 15:29:09 GMT

Could you confirm the functionality of this DDE replacement? From what I see it is only for creating Excel files, much like the rest of the ODS system. It doesn't for instance, have the ability to go into existing Excel files and make alterations, at least from what I could see.

My original post still stands, Excel has its pluses, its a great frontend for data entry and such like, but it is not a good backend. If you have to work with Excel files, then its perhaps a good idea to invest some time in learning Open Office, an dperhaps other tools like C# or python which can alter directly existing files. ODS Excel is good for writing out data from SAS to Excel, but thats really about it.

I would really consider what the purpose is, look at data, data entry, and reporting as different elements as there a many ways of doing each and not necessarily one which is great at all of them.

Re: put statement and line break using /

RW9 — Thu, 16 Feb 2023 11:32:22 GMT

Thats odd, can't think of anything directly unless you had a long line which split, or some sort of special character or break. Cant find a system option for it. When I do:

data temp;
  put 'line 1' //;
  put 'line 2';
run;

I get:

line 1  <-
<-
<-
line 2

The reason being is that / moves the cursor to the first char of the nest row, as does put. So print line 1, move to next (/), move to next (/), move to next (put), print line 2. So two / should effectively create two blank rows.

Re: SAS code

RW9 — Thu, 16 Feb 2023 11:19:59 GMT

Not tested (pop test data in the form of a data step please):

data want;
  set have;
  retain num_times 0;
  if lag(flag)=0 and flag=1 then num_times=sum(num_times,1);
run;

Last record would have your total number of times. You could also do it via sql to join lowest previous date to current record as well.

Re: How to read from pdf to SAS

RW9 — Thu, 16 Feb 2023 11:17:54 GMT

Exactly. How can you bring in things which aren't text. Checkbox could be a PDF object, or a picture. End of the day, you could learn javascript in PDF and export it, or possibly find a third party library to get it (perhaps at a quick search: https://stackoverflow.com/questions/55777812/python-pdf-how-to-read-from-form-elements-like-checkbox), but its going to take a fair bit of effort. Go back to source data is really the best option.

Re: Removing last word with length 1 or 2

RW9 — Thu, 16 Feb 2023 08:57:39 GMT

You could simply scan the last word by space, and check if length is 1 or 2:

data want;
  str="abc ert werty we";
  if lengthn(scan(str,-1," ")) in (1,2) then check=1;
run;

Re: How to read from pdf to SAS

RW9 — Thu, 16 Feb 2023 08:48:52 GMT

PDF is a proprietary binary file. SAS cannot read much else other than the plain binary code which unless you understand and can parse it, is of no use. PDF is not a data format, its a render destination. Whilst there are certain packages in python/R and other languages languages to do certain things, even if you do manage to get anything useful out of it would require full QC and probably a lot of work/processing. I would highly recommend either returning to source data which is the preferred method, or worst case, type things in manually. Ultimately PDF is a dreadful format, as far from open as possible, so avoid it as much as possible.

Re: desire date and time not reflecting

RW9 — Thu, 16 Feb 2023 08:44:29 GMT

Your post does not include enough information to help answer the question. Posting test data in the form of a datastep is required. For instance, there is no example of what dod or adt is?

Your text is all in opposites, you say dod is numeric, but line 221 is clearly telling you an issue as you have compress() which requires a text var, with dod var which is numeric.

Also, there is no error, only telling you you have differing types for your variables. If its not working, try simplifying your code down, only do one function per run, e.g. see what the compress does, then what the inputs do, this is stepwise error analysis. Only build up long complicated multi calls when you know bits work.

Re: How to read a macro variable value and convert it in SAS data set

RW9 — Wed, 15 Feb 2023 15:33:51 GMT

Not tested, but something like:

%let x =%nrstr(f588c556-15c4-4d59-b9df-88b2c655a950,06d54597-fc35-459b-bc5e-1075e9643a1d,06d54597-fc35-459b-bc5e-2137e9643a1d);

data want;
  do i =1 to countw("&x.",",");   
    result=scan("&x.",i,",");
    output;
  end;
run;

Essentially count how many comma separated items, then substr each by delimiter comma and output.

To add, macro variables are not th eplace to store data, there are many ways to store it, json, xml, datasets etc.

Re: Add a row in empty table

RW9 — Wed, 15 Feb 2023 15:16:59 GMT

If your table is empty of rows, then output will not create a row. There are several methods, such as appending data to the table, or insertin via sql, e.g.

/* Create empty table for example */
proc sql;
  create table empty (id num, somme num);
quit;

/* Use insert sql method */
proc sql;
  insert into empty set id=1;
quit;

/* Use append */
data inter;
  id=1;
run;
data empty;
  set empty inter;
run;

Re: Excel Sheet Names with multiple variables with Proc Freq

RW9 — Sat, 22 May 2021 17:19:26 GMT

I would advise to split the program logic out. Something like three distinct steps
Get data

Process data

Report data

So in your example, I would process the survey data such that you get a dataset out from it:

ods output <object>=workingdata;
proc surveyfreq data=surveydata missing nosummary;
stratum stratum;
weight FinalWt;
tables cohort*(q0001 q0002)/cl nostd;
run;

In the above you would need to get the object name from an ods trace on; statement which will show you all the objects which are created (ods trace off; after). The objects can all be saved and processed further - this is a big plus to the process as you can format and manipulate as you want.

Then when you have the data all nicely processed, then open the Excel ods and report the data.

Re: produce docx file

RW9 — Sat, 22 May 2021 17:15:09 GMT

Interesting that after all this time they have made DOCX a destinatation. I would still however stand by my original posts. RTF being a far better format for outputs. The reasons, RTF is markup, you can insert markup via report, or in data, its open, plain text file format makes it machine and programmatically readable, can be opened and parsed just like a word document. So has all the functionality, without the drawbacks of docx (which is a zip file with XML parts). If you need PDF from it, you can directly convert to PDF from RTF. Only if there was no other option would I be generating docx off the bat.

Re: multiple left join

RW9 — Thu, 06 May 2021 10:06:05 GMT

If your datasets are large, then there is a good case for not using SQL at all. SAS implementation of SQL (from what I recal) is that it is all done be loading and writing into intermediary tables, so when data gets larger then it takes longer to process.

The question raised here was "how do I do xyz", not should I be doing "xyz". IN case of big datasets I would set both sets of data together with a category defining which dataset of orign, then merge the two sets of data using a datastep merge, this would be the most efficient method to get the final result.

Re: File Size calculation using SAS program

RW9 — Fri, 22 Jan 2021 15:03:43 GMT

Please avoid posting new questions in very old answered questions. Open a new thread if you have a new question.

In terms of my code, the pipe is a means of sending output from a command sent to the operating system (in this case a dir command), back to a dataset. So the filename sets this up, the datastep and infile actually run the command in your OS, and then the output is captured and read back in via the input.

Proc http to Rave returns error

RW9 — Tue, 15 Dec 2020 10:26:08 GMT

Hi,

I am attempting to retrieve CSV files from Rave via proc http. This work via browser, i.e. post uri, enter login, it downloads the csv file. However when I run proc http with same uri and login details I get the following error message:

ERROR: Windows SSL error -2146892018 (0x80090326) occured at line 2320, the error message is 'The message recieved was unexpecedly or badly formatted." 

ERROR: Windows SSL error -2146892018 (0x80090326) occured at line 2320, the error message is 'The message recieved was unexpecedly or badly formatted."

Error: Encryption run-time execution error

Note that the code is run on destktop SAS version 9.4 1M2 running on Windows 10 Enterprise.

In a previous role we had this code working correctly, although we did have to add proxyhost and proxyport in.

Is this something that anyone else has encountered or come up with a solution for? Is it related to needing proxy information as our IT state there is no proxy.

Re: Can we get count of rows in a macro variable for a blank dataset

RW9 — Fri, 13 Nov 2020 10:18:42 GMT

Seeing as you necro'd an old thread, I thought I would point out:

SAS Creates macro variables at the end of each step:

https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=mcrolref&docsetTarget=p0t267pk8lwx3en1gn9fywjgni8y.htm&locale=en

And SQL is sqlobs.

So if you need it inline with your code then you can use that after the step. If you need observations elsewhere for some reason, then there is the SASHELP.VTABLE view which holds observations for all data tables (except external linked databases), so you can always get the obs from there. Depends on what you need it for. Rarely useful to recreate to have your own macro variables.

Re: PDF output with one bookmark

RW9 — Fri, 29 May 2020 09:26:51 GMT

Hi,

Well, I will give you the points, as this code did what was needed. However the moment I add anything to it then it breaks. For instance, try adding: ods escapechar="^"; before that, I then get no graph output. Adding RTF ods around that, i.e. to get the same output to two ods leads to a warning, and empty pdf output.

I will stick with the create an RTF file, then use Adobe to create PDFs from there, it seems to be the only method which reliably works. I would suggest though that SAS add something like this in as often people want to generate a file with just the one bookmark form compilation into other documents, I mean its Adobe default behaviour so its very strange that it is not available from SAS.

Thanks