Hi.
I have a variable with name "specimen#" given data format like A0N1A114A, I hope to get the fourth number(1 in the case) and create a new column. please help me.
Thanks
/***** TRANSFERRING DATA INTO D:\TEMP\MOCK.XLS ***/
data MOCK;
set work.mock; /* 7 */
file lager; /* 8 */
if _n_ = 1 then put /* 9 */
'date' '09'X 'count1' '09'X 'count2' '09'X 'count3' '09'X;
put date '09'X i '09'X j '09'X k '09'X;
/* 10 */
run;
*** END OF SAS CODE ***
Explanation
1) The Dos Command to open Excel
2) Creating a ‘DDE’ for excel called Beer
3) Creating a ‘DDE’ for excel sheet Mock.xls called lager
4) Give the computer time to open excel by letting the SAS system rest for 5 seconds
5) Tells SAS to use the DDE command Beer
6) Due to the DDE command Beer, we can now open ‘d:\temp\mock.xls’. Before telling SAS to carry out a command, we would need to put what is required within “put ‘[……]’
7) This is the data set created at the beginning
8) This a new DDE command
'excel|[mock.xls]sheet1!r1c1:r30000c10'
it tells SAS that it can use mock.xls, sheet1 between row 1 and column 1 (A) to row 30000 and column 10 (J) to input the data from ‘work.mock’. If not enough space is allocated then SAS will not be able to input all the data required. E.g a SAS table with 100 columns but you only specify 50 columns for excel, the excel sheet will miss the last 50 columns.
9) This bit is not compulsory but will give your column headings if used. The words in inverted commas represent the column headings whereas the ‘09’X represents the end of the column and to start a new column.
10) The column names of the data set. Please not that the column names you provide for excel must be in the same order as the column you are providing in the data set or confusion will be caused.
data _null_;
file beer;
put '[open("d:\temp\mock.xls")]';
stop;
run;
/***** TRANSFERRING DATA INTO D:\TEMP\MOCK.XLS ***/
data mock;
set work.mock;
file lager;
if _n_ = 1 then put
'date' '09'X 'count1' '09'X 'count2' '09'X 'count3' '09'X;
put date '09'X i '09'X j '09'X k '09'X;
run;
data _null_;
slept = sleep(5);
run;
data _null_;
file cmds;
put '[error(false)]'; /*(1)*/
put '[workbook.activate("Sheet2")]'; /*(2)*/
stop;
run;
/***** TRANSFERRING DATA INTO D:\TEMP\MOCK.XLS sheet2 ***/
data mock;
set work.mock;
file wine; /*(3)*/
if _n_ = 1 then put
'Identification Number' '09'X 'Score' '09'X 'marks needed' '09'X 'month' '09'X ;
put i '09'X j '09'X k '09'X date '09'X ;
run;
data _null_;
slept = sleep(5);
run;
data _null_;
file beer;
put '[error(false)]';
put '[save.as("d:\temp\mock1.xls")]'; /*(4)*/
put '[close()]'; /*(5)*/
put '[quit]'; /*(6)*/
stop;
run;
As can be seen from above, a new DDE command has been introduced called wine.
'excel|[mock.xls]sheet2!r3c2:r30000c10'
This tells the computer to use mock.xls, sheet2 and this time to start at row 3 column2 (B) to row 20000 column 10 (J).
1) put '[error(false)]'; -tells the computer not to prompt for confirmation
2) tells the computer to activate a certain sheet within an excel sheet
3) the new DDE command specifying sheet2 (all ready activated)
4) Saving the spreadsheet as something new
5) Closes said spreadsheet (but not excel)
6) Closes Excel
***** PLEASE NOTE THAT D:\TEMP\MOCK.XLS IS STILL BLANK AND DOES NOT HAVE ANY DATA STORED INTO IT ******
Formats
Now that a new spreadsheet called mock1.xls has been created we can open it and make the column heading bold, change it’s font size and type of writing style using,
put '[font.properties("Arial", "Bold",10,,,,,,,1)]';
As we can see this makes the cell selected ‘Arial’ writing style, Bold type and size 10. The number at the end represents different colours. The commas are important as they represent different commands but are unused.
1=black, 2=white, 3=red, 4=green, 5=blue, 6=yellow, 7=pink, 8=light blue, 9=brown.
The numbers do go higher but these colours should suffice
For sheet1 - font size 12, bold and black
For sheet2 - font size 12, bold and blue
options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"';
data _null_;
slept = sleep(5);
run;
filename beer dde 'excel|system';
data _null_;
file beer;
put '[open("d:\temp\mock1.xls")]';
x=sleep(3); /** allows sas to sleep for 3 seconds so that mock1.xls can open ***/
put '[workbook.activate("Sheet1")]';
stop;
run;
data _null_;
file beer;
put '[select("R1")]'; /* selected all of the row **/
put '[font.properties("Arial", "Bold",12,,,,,,,1)]';
put '[workbook.activate("Sheet2")]';
put '[select("r3c2:r3c5")]'; /* selected just the cells **/
put '[font.properties("Arial", "Bold",13,,,,,,,5)]';
stop;
run;
As shown with the code, you can choose whole rows or just certain cells. To highlight a whole column we would use
put '[select("C1")]';
Sheet2 now looks a bit of a mess and needs tidying
If we would like to keep the cells at size 13 and bold then we would need to make the rows longer, wrap the text and fix the width of the column, either by best fit or by fixed amount. The next bit follows the above pattern so do not get to confused with the code.
As we have excel sheet open all ready and on mock1.xls, we can just run the next bit just to update sheet 2.
data _null_;
file beer;
put '[workbook.activate("Sheet2")]';
put '[select("R3")]';
put '[row.height(30)]';
put '[Alignment(3,True,2,0)]'; /* Center Justify Headings and Wrap Text */
put '[column.width(0, "c2:c5", false,3)]'; * 3 = autofit ;
put '[column.width(15,"c2",false)]';
put '[column.width(11,"c4",false)]';
put '[select("r1c1")]';
stop;
run;
As can be seen from above. First we activate sheet2, just in case sheet1 is activated on mock1.xls.
All of Row 3 is selected next, then the command, centres and wraps the text. (if we do not want to wrap the text the we would use false [Alignment(3,False,2,0)]
The height of the row is not high enough yet, so will have to change this, in this case 30.
Where as auto-fit columns can work for the majority of columns, sometimes, will need to use code to amend the ‘best-fit’ so that the headings are readable/look nice
Lastly, the put '[select("r1c1")]'; stops the row begin highlighted and moves to row1 column1.
Colouring cells
The changing the colour of cells follows the same pattern as shown above.
Suppose we would want to make the table in sheet1 green, but the headings blue then we would use the following
/** changing the colour of cells **/
data _null_;
file beer;
put '[error(false)]';
put '[workbook.activate("Sheet1")]';
x = sleep(3);
put '[select("R1C1:R1C4")]';
put '[patterns(1,,5,true)]';
x = sleep(1);
put '[select("r2c1:r11c4")]';
put '[patterns(1,,4,true)]'; /* first number does crazy stuff **/
put '[select("r1c1")]';
stop;
run;
As before we select the cells we would want changed, then use
put '[patterns(1,,5,true)]';
the 5 represents the colour as shown before.
data _null_;
file beer;
put '[error(false)]';
put '[workbook.activate("Sheet1")]';
x = sleep(3);
PUT '[select("c3")]';
put '[insert(3)]'; /* can use 1,2,3, or 4 here have not sen any difference specifies insert*/
PUT '[select("r5")]';
put '[insert(2)]'; /* can use 1,2,3, or 4 here have not sen any difference specifies insert*/
put '[select("r1c1")]';
stop;
run;
As we can see inserting a new row into here does nt seem like a good idea, so we can delete a row.
data _null_;
file beer;
put '[error(false)]';
put '[workbook.activate("Sheet1")]';
x = sleep(3);
PUT '[select("r5")]';
put '[EDIT.DELETE(2)]';
put '[select("r1c1")]';
stop;
run;
Again this follows the similar pattern from before, which can easily be amended to choosing columns PUT '[select("c5")]';, if so wish.
date count1 count2 count3
Feb-06 1 11 9
Mar-06 2 12 8
Apr-06 3 13 7
May-06 4 14 6
Jun-06 5 15 5
Jul-06 6 16 4
Aug-06 7 17 3
Sep-06 8 18 2
Oct-06 9 19 1
Nov-06 10 20 0
Clearing specific cells
data _null_;
file beer;
put '[error(false)]';
put '[workbook.activate("Sheet1")]';
x = sleep(3);
PUT '[select("r2c2")]';
put '[clear]';
put '[select("r1c1")]';
stop;
run;
These can be done in 2 ways:
1) within your SAS data set
2) within excel using DDE
Using SAS.
data work.mock2;
set work.mock;
f = compress('='||i||'*'||j);
run;
options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"';
data _null_;
slept = sleep(5);
run;
filename beer dde 'excel|system';
data _null_;
file beer;
put '[open("d:\temp\mock1.xls")]';
x=sleep(3); /** allows sas to sleep for 3 seconds so that mock1.xls can open ***/
put '[workbook.activate("Sheet3")]';
stop;
run;
data mock2;
set work.mock2;
file bitter;
if _n_ = 1 then put
'date' '09'X 'count1' '09'X 'count2' '09'X 'count3' '09'X 'formula' '09'X;
put date '09'X i '09'X j '09'X k '09'X f '09'X;
run;
data _null_;
file beer;
put '[error(false)]';
put '[workbook.activate("Sheet1")]';
x = sleep(3);
PUT '[select("r2c3")]';
put '[formula.fill("=RC[+1]*RC[-1]","R2C3:R11C3")]';
put '[select("r1c1")]';
stop;
run;
As we may notice that all the command follow the same pattern using dde, so the last part of this will just concentrate on the code, with a heading describing its function
Freezing panes
data _null_;
file beer;
put '[freeze.panes(true,0,1)]'; /** in the format of column, row, freezez panes on row 1 **/
stop;
run;
but can also freeze row and columns at same time.
data _null_;
file beer;
put '[freeze.panes(true,2,1)]'; /** in the format of column, row, freezez panes on row 1 **/
stop;
run;
Inserting a new work sheet
data _null_;
file beer;
put '[workbook.insert(1)]';
run;
Deleting a work sheet
data _null_;
file beer;
put '[workbook.delete("Sheet4")]';
run;
Renaming a work sheet
This bit is not straight forward as requires a bit of a macro. In this example we will be naming sheet1 to champagne.
data _null_;
file beer;
put '[workbook.next()]';
put '[workbook.insert(3)]'; /* creates a macro sheet*/
run;
filename vodka dde 'excel|macro1!r1c1:r100c1' notab;
data _null_;
file vodka;
put '=workbook.name("sheet1", "champagne")';
put '=halt(true)';
put '!dde_flush';
file beer;
put '[run("macro1!r1c1")]';
run;
data _null_;
file beer;
put '[workbook.delete("Macro1")]';
run;
Hopefully this covers the basics of using DDE, as a way of not using excel macros. Most of the codes can be combined into one big data null step, as shown in the last example.
data _null_;
file beer;
put '[open("d:\temp\mock.xls")]';
stop;
run;
/***** TRANSFERRING DATA INTO D:\TEMP\MOCK.XLS ***/
data mock;
set work.mock;
file lager;
if _n_ = 1 then put
'date' '09'X 'count1' '09'X 'count2' '09'X 'count3' '09'X;
put date '09'X i '09'X j '09'X k '09'X;
run;
data _null_;
slept = sleep(5);
run;
data _null_;
file cmds;
put '[error(false)]';
put '[workbook.activate("Sheet2")]'; /*(2)*/
x=sleep(3);
stop;
run;
/***** TRANSFERRING DATA INTO D:\TEMP\MOCK.XLS sheet2 ***/
data mock;
set work.mock;
file wine;
if _n_ = 1 then put
'Identification Number' '09'X 'Score' '09'X 'marks needed' '09'X 'month' '09'X ;
put i '09'X j '09'X k '09'X date '09'X ;
run;
data _null_;
slept = sleep(5);
run;
data _null_;
file beer;
put '[error(false)]';
put '[workbook.activate("Sheet1")]';
put '[select("R1")]'; /* selected all of the row **/
put '[font.properties("Arial", "Bold",12,,,,,,,1)]';
put '[workbook.activate("Sheet2")]';
put '[select("r3c2:r3c5")]';
put '[font.properties("Arial", "Bold",13,,,,,,,5)]';
put '[select("R3")]'; /* Center Justify Headings and Wrap Text */
put '[row.height(30)]';
put '[Alignment(3,True,2,0)]';
put '[column.width(0, "c2:c5", false,3)]'; * 3 = autofit ;
put '[column.width(15,"c2",false)]';
put '[column.width(11,"c4",false)]';
put '[select("r1c1")]';
put '[workbook.activate("Sheet1")]';
x = sleep(3);
put '[select("R1C1:R1C4")]';
put '[patterns(1,,5,true)]';
x = sleep(1);
put '[select("r2c1:r11c4")]';
put '[patterns(1,,4,true)]'; /* colors 1=black 2=white 3=red 4=green 5=blue 6=yellow 7=pink 8=lightblue 9=brown */
put '[select("r1c1")]';
put '[workbook.activate("Sheet1")]';
PUT '[select("c3")]';
put '[insert(4)]';
put '[workbook.activate("Sheet1")]';
put '[freeze.panes(true,0,1)]'; /** in the format of column, row, freezez panes on row 1 **/
PUT '[select("r2c3")]';
put '[formula.fill("=RC[+1]*RC[-1]","R2C3:R11C3")]';
put '[select("r1c1")]';
stop;
run;
filename namey dde"excel|sheet1!r1c3";
data _null_;
file namey;
put 'formula';
stop;
run;
data _null_;
file beer;
put '[workbook.next()]';
put '[workbook.insert(3)]';
run;
filename vodka dde 'excel|macro1!r1c1:r100c1' notab;
data _null_;
file vodka;
put '[error(false)]';
put '=workbook.name("sheet1", "champagne")';
put '=halt(true)';
put '!dde_flush';
file beer;
put '[run("macro1!r1c1")]';
X=SLEEP(3);
put '[workbook.delete("Macro1")]';
put '[save.as("d:\temp\mock1.xls")]';
put '[close()]';
put '[QUIT]';
stop;
run;
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.