Hw to create a new variable from old var.

[on818come]

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

 

[on818come]

Another question: how to keep variable's label name in Excel sheet after exporting data.

 

[alunbrain]

Hi

think for part 1 all you need is a sub string command (substr)

data tmp;
set ...;
newvar= substr(specimen#,4,1);
run;

for excel please have a look into one of my training docs using DDE commands
having trouble attathcing it, so copied and pasted below



DDE COMMAND
SAS AND EXCEL




Abstract

The DDE (Dynamic Data Exchange), allows SAS to amend excel sheets without invoking macros, or physically amending them.

DDE allows you to select certain cells, rows or columns, format them: colour, size, wrap text to name a few.

This allows the user to by pass invoking macros, when all the macro does is make the report look pretty.


Tthe data set used

data work.mock;
format date monyy5.;
do i = 1 to 10;
j=i + 10;
k = j - i*2;
date = intnx('month', '01jan06'd, i);
output;
end;
run;



Beginning

DDE commands require the use of SAS filename statements:

filename yourref dde 'excel|system';

yourref, can be anything to outtoxl (out to excel) or simple names like curry, beer, ringo etc…








Starting Excel from SAS

options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe" ';


data _null_;
slept=sleep(5);
run;


options stops the DOS command box from appearing asking you to type in EXIT

in SAS, anything that starts with an ‘x’ denotes a DOS command, in this case, start open excel.

Slept = sleep5 gives the computer time to open excel.

To open a workbook, for this exercise, a worksheet called mock is stored in d drive in the temp folder.

There are 2 ways to open this spreadsheet.

1)
x '"c:\program files\microsoft office\office\excel.exe"
"d:\temp\mock.xls"';

2) Since we are using DDE commands


options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"';

filename beer dde 'excel|system';

data _null_;
file beer;
put '[open("d:\temp\mock.xls")]';
stop;
run;

the DDE command reference is called ‘beer’ so there can be no mistaking its name.

*** AS SOON AS WE HAVE OPENNED EXCEL WE CAN UPDATE THE SHEETS USING THE DDE WITHOUT RE-RUNNING PREVIOUS CODE ***




Transferring data from SAS to Excel

As mentioned above we have already created a sas data set called work.mock.

This process allows us to rename columns on excel sheet and transfer the data across and also nominate where we want the table to start.

THE SAS CODE


options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"'; /* 1 */

filename beer dde 'excel|system'; /* 2 */

filename lager dde 'excel|[mock.xls]sheet1!r1c1:r30000c10' notab lrecl=20000; /* 3 */


data _null_;
slept = sleep(5); /* 4 */

run;


data _null_;
file beer; /* 5 */

put '[open("d:\temp\mock.xls")]'; /* 6 */

stop;
run;

/***** 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.


With step 9

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












To run the code without step nine.



options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"';

filename beer dde 'excel|system';
filename lager dde 'excel|[mock.xls]sheet1!r1c1:r30000c10' notab lrecl=20000;

data _null_;
slept = sleep(5);
run;


data _null_;
file beer;
put '[open("d:\temp\mock.xls")]';
stop;
run;

/***** TRANSFERRING DATA INTO D:\TEMP\MOCK.XLS ***/

data outtoxl1;
set work.mock;
file lager;
put date '09'X i '09'X j '09'X k '09'X;
run;




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









*** FOR FUTURE WORK WE WILL BE INCLUDING STEP NINE ***

INPUTTNIG DATA INTO MORE THAN ONE WORK SHEET AND SAVING



/*** TRANSFERRING DATA SECTION ***/


options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"';

filename beer dde 'excel|system';
filename lager dde 'excel|[mock.xls]sheet1!r1c1:r30000c10' notab lrecl=20000;
filename wine dde 'excel|[mock.xls]sheet2!r3c2:r30000c10' notab lrecl=20000;

data _null_;
slept = sleep(5);
run;


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





Results


Sheet1


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




Sheet2 (note column2 and row 3 where the data begins)




Identification Number Score marks needed month
1 11 9 Feb-06
2 12 8 Mar-06
3 13 7 Apr-06
4 14 6 May-06
5 15 5 Jun-06
6 16 4 Jul-06
7 17 3 Aug-06
8 18 2 Sep-06
9 19 1 Oct-06
10 20 0 Nov-06





***** 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.



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



/*** INSERTING A NEW COLUMN ***/

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;



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




/*** deleting a new column /row **/

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;



date count1 count2 count3
Feb-06 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


UNDO

As can not see much point in doing at this point, if we would like to undo what we had previously just done then


data _null_;
file beer;
put '[error(false)]';
put '[undo()]';
stop;
run;










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





Formulas

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;


filename bitter dde 'excel|[mock1.xls]sheet3!r1c1:r30000c10' notab lrecl=20000;


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;



date count1 count2 count3 formula
Feb-06 1 11 9 11
Mar-06 2 12 8 24
Apr-06 3 13 7 39
May-06 4 14 6 56
Jun-06 5 15 5 75
Jul-06 6 16 4 96
Aug-06 7 17 3 119
Sep-06 8 18 2 144
Oct-06 9 19 1 171
Nov-06 10 20 0 200




The second way DDE

Back to using mock1.xls


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;









date count1 count2 count3
Feb-06 1 11 11 9
Mar-06 2 24 12 8
Apr-06 3 39 13 7
May-06 4 56 14 6
Jun-06 5 75 15 5
Jul-06 6 96 16 4
Aug-06 7 119 17 3
Sep-06 8 144 18 2
Oct-06 9 171 19 1
Nov-06 10 200 20 0


To divide use
put '[formula.fill("=RC[+1]/RC[-1]","R2C3:R11C3")]';

RC[+1] = 1 column to the right
RC[-1] = 1 column to the left


Naming the column


filename namey dde"excel|sheet1!r1c3";

data _null_;
file namey;
put 'formula';
stop;
run;



date count1 formula count2 count3
Feb-06 1 11 11 9
Mar-06 2 24 12 8
Apr-06 3 39 13 7
May-06 4 56 14 6
Jun-06 5 75 15 5
Jul-06 6 96 16 4
Aug-06 7 119 17 3
Sep-06 8 144 18 2
Oct-06 9 171 19 1
Nov-06 10 200 20 0





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.

Refernce

http://www2.sas.com/proceedings/sugi27/p190-27.pdf#search="sugi 27 creating custom excel"

/** DDE COMMANDS CODE **/



data work.mock;
format date monyy5.;
do i = 1 to 10;
j=i + 10;
k = j - i*2;
date = intnx('month', '01jan06'd, i);
output;
end;
run;

data _null_;
slept=sleep(5);
run;

/*** TRANSFERRING DATA SECTION ***/


options noxwait noxsync;
x '"c:\program files\microsoft office\office\excel.exe"';

filename beer dde 'excel|system';
filename lager dde 'excel|[mock.xls]sheet1!r1c1:r30000c10' notab lrecl=20000;
filename wine dde 'excel|[mock.xls]sheet2!r3c2:r30000c10' notab lrecl=20000;

data _null_;
slept = sleep(5);
run;


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;

 

[on818come]

thx