08-29-2012 01:38 PM
I have a one Base SAS Code file (Program) if i run that code I will get a 450 Columns data.....
In that 450 Columns I need only 27 Columns need CSV or XML format to this should done every 15 days once......How do I can any one help me please
I have a SAS Base, SAS EG, SAS MC please guide & help me.
If it's Base SAS Code that would be great
08-29-2012 03:06 PM
Thank you for your reply Docaduke
And please read my question completely 'Schedule' and it should run every 15 days once automatically (as per the server date or system date) ...
08-29-2012 02:14 PM
Your description is a good start, but does not contain enough information to provide useful assistance.
Knowing that your program gives you 450 columns of data is helpful, but seeing the code would be most helpful. For example, what procedure is being used for your output? PROC PRINT? PROC REPORT? PROC SQL? DATA step? I would imagine that a report would be hard to read with 450 columns of data. I wonder, too, how many observations (or rows) are in your dataset that is being displayed?
Next, let me break down your requirements. I read that you want/need 3 things. You said:
In that 450 Columns
--1) I need only 27 Columns
--2) need CSV or XML format to this
--3) should done every 15 days once......
For #1, the answer depends on your existing code. For example, if PROC PRINT, then you would use a VAR statement to limit the columns that you see. If PROC REPORT, then you would use a COLUMN statement to limit the columns that you see. For PROC SQL, then you would use your SELECT statement/clause to limit the columns. If DATA step, then DROP or KEEP methods would be used. #1 cannot be answered given the information you have provided. Can you post your code or a simplified version of your code? For example, SASHELP.CLASS has 5 variables/columns and 19 observations. The 5 variables are NAME, AGE, HEIGHT, WEIGHT and SEX. If I only want to display NAME, AGE and HEIGHT, my PROC PRINT might look like this:
proc print data=sashelp.class;
var name age height;
For #2) Again, if you only have BASE SAS and EG, then EG has methods to export output to Excel format. XML format is a very broad requirement -- we need more specific info -- what kind of XML? Do you mean Excel Spreadsheet Markup Language XML as established in Office 2003? Do you mean the XBRL reporting language or Justice XML or CDISC XML or Chem XML or ??? ? Do you mean the SASReport XML format? Do you mean some other XML format? Also, the answer to #2 is tied to the whether you have the SAS BI Platform or not. You say that you have SAS MC and by this I think you mean SAS Management Console. If you are running code in the context of the BI platform it is useful to know whether you are using the SAS Add-in for Microsoft Office and/or whether you are using a SAS Stored Process. If you are NOT using the BI Platform and are ONLY using BASE SAS and EG, if you do not want your CSV file created using EG menu methods (or send to Excel methods). Then using ODS destination statements, would be one way that you would create a CSV file. For example:
ODS _ALL_ CLOSE;
ODS CSV file='C:\temp\myfile.csv';
ODS CSV CLOSE;
But that ODS method might not be appropriate if you have the BI Platform and/or the SAS Add-in for Microsoft Office.
For #3) Again, not entirely clear what you mean when you say that this process should be "done every 15 days once". If you have the BI Platform and are using EG, then you can build a project and grab the code and create a job or task that could be scheduled using your operating system scheduler. Every 15 days sounds like you want something scheduled twice a month. Depending on your operating system and scheduling software, you'd have to have a working program first and then investigate how to get it scheduled with your particular installation and resources. If you only have BASE SAS, then you can look at the SAS Companion documentation for your operating system to find out how to automate the submission of a .bat, .cmd or .exe file. If you have Enterprise Guide and want to automate an Enterprise Guide project to run, then you could build a custom task using .NET technology.
If you are talking about a scheduled job, then the answer to this question probably falls outside the realm of BASE SAS code. If you only need to make the program more generic and repeatable so you can run the program every two weeks, then you might need to use SAS Macro programming, but again, that is more advanced and may or may not be necessary, depending on the answers to #1 and #2.
You might want to open a track with Tech Support, where they could collect all the relevant information; look at all your code; look at your configuration information and make a recommendation based on having all the information. To open a track with Tech Support, fill out the form at this link:
08-29-2012 03:25 PM
Thank you for your reply
Well Couple of details here....
#1) It is a PROC SQL
#2) It has 5k OBS
#3) Need a 'Chem XML' Ex:- (ParentNode, ChildNode ) (What r the columns i'm getting '27' that it should come on CSV & XML)
#4) Yes we have a BI (SAS MC) but I need this in Base sas CODE or any other options in SAS EG
#5) This is not a SAS DI JOB. This is completely done by hand CODE.
#6) From that Code I will get 450 columns as i told that in that i Need a only 27 (Selected) from that i need a 'Scheduler' to run every 15 days once as per the server time or system time.
08-29-2012 10:05 PM
Wow! I have not heard of Chemical Markup language XML or ChemML or CML for a long time. I referenced it in my post because I showed an example of the Jumbo browser plug-in used for CML in one of my papers.
My paper on Markup Languages: http://support.sas.com/rnd/papers/sugi29/markup-basics.pdf (see the bufotenin.xml example in Chemical Markup Language on page 8 of my paper)
So when you say that you are using Chemical Markup Language XML with ParentNode and ChildNode, I do not find that in the spec. But it seems to me that creating CSV versus creating Chemical Markup XML are two very different things.
Since you said you were using SQL, then you would need to alter your SELECT statement to get only 27 of the 450 columns. More difficult will be to get your data into a structure so that you can create the correct XML. Needless to say, SAS does not automatically have a way to create Chemical Markup Language XML. You would have to code your own method of creating the output XML file. Your choices are:
1) writing a Data Step program
2) creating a custom TAGSET template for use with the XML Libname Engine and/or with ODS
However, I think you will need a bit more help in order to make an XML file. You need to show someone a sample of your data and the original data structure; they then have to understand the Chemical Markup Language schema as well as you understand the schema to understand how you need to translate your data structure into XML structure. Then if you can use a simple XML form, such as that created by the SAS XML Libname engine, you could generate a SAS table in simple XML format; however, if your schema requires you to have very hierarchical XML elements, then you will need an XML Map or another method to translate your structure into the XML structure you need. I believe that you will need help from Tech Support on this part of your question. And, given that you do not have a working program yet, discussing how to schedule your program to run every 15 days seems premature to me. I still think your best bet for help is to work with SAS Tech Support. Folks can give you snippets of code and you have received some examples for how to make the CSV file but the XML file could be a challenge.
If you truly do have the BI Platform, SAS Management Console and the Platform Scheduler, then your assertion that you want to do this in "hand code" seems like you are trying to cause yourself more work. But at any rate, since I don't know Chem ML at all, the only part of your question that I really could contribute to would be the CSV part and you already have one possible solution for that. Using ODS CSV with PROC PRINT would be another solution, but I don't want to introduce a new possibility into the mix at this point.
Have you looked at the SAS Companion for your operating system (SAS Companion for Windows, SAS Companion for Unix, SAS Companion for z/OS)? There should be instructions in there for how to create a program that can be executed in batch mode. (But, first you have to have a program ...)
08-29-2012 02:16 PM
To write a CSV file with the variable names on the first line, you could use :
file "C:\my27VarFile.csv" DSD LRECL=2048;
if _n_=1 then put 'myVar1' 'myVar2' ... 'myVar27';
put myVar1 myVar2 ... myVar27;
or use the export proc :
proc export data=my450varDataset(keep=myVar1 myVar2 ... myVar27)
08-29-2012 03:10 PM
Thank you for your reply PG
Some how it's ok. But here my task is to 'Schedule' and it should run every 15 days once automatically (as per the server date or system date) ...