topic Re: Copy column from one dataset to another in SAS Programming

Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 11:29:47 GMT

I need to copy one column from one dataset to another dataset. Moreover, I need to do the same task many times (say I need to copy column1 from datase_1 to dataset_a, column1 from dataset_2 to dataset_b, and so on. How can I do this task? Should I use macro such as %macro, %copy etc?

Further, I need to subtract that copied column from every variable in the dataset. Say, dataset_a has variables: date, Firm1, Firm2, … Firm100. Now column1 from dataset_1 will be copied to dataset_a. So dataset_a will look like: Date, Firm1, Firm2, … Firm100, column1. Now column1 will have to be subtracted from Firm1, Firm2 , … Firm100. In this way, column1 from dataset_2 will have to copied to dataset_b and that copied variable will have to be subtracted from other variables in dataset_b.

Re: Copy column from one dataset to another

Amir — Fri, 21 Feb 2014 12:17:41 GMT

Hi,

Does the column you want to copy always have the same number of observations as the data set you want to copy to. If not then you'll need to specify what should happen when there is a difference in observation count.

A simple example of a macro where the observations are the same would be:

data class(rename=(age=years));

set sashelp.class;

run;

%macro addcol(ds1,col,ds2,newds);

data &newds;

set &ds2;

set &ds1(keep=&col);

run;

%mend addcol;

%addcol(class,years,sashelp.class,class2);

Regards,

Amir.

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 12:53:42 GMT

"Copy a column" is not a normal data management activity. From your later description is looks like you want to merge on a new variable based on values of your key variable DATE.

data want ;

merge old new (keep=date diff) ;

by date;

array firms firm1-firm100 ;

do over firms; firms=firms - diff; end;

run;

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 15:04:35 GMT

It's not working. Let me explain again. From the previous code (you gave me) I select the variables and then performed the equations. The last equation is ret(i) = ……… which generates returns for each variable such as ret1 ret2 etc. Now I want to subtract each of these returns from average_ret. This average_ret is in another dataset. How can I do that?

Further, when I subtract ret1 - average_ret, ret2 - average_ret etc. then I cannot identify the main variables. I also need to identify the return difference for each main variable. How can I do that?

Again, this variable list is for one dataset. I want to do this task for multiple datasets. So do I need to run code for each dataset separately?

Below is your previous code that you gave me:

%let varlist = CA45245E1097 ... US98974P1003 ; /***I wrote all the variable names***/

%let nvar = %sysfunc(countw(&varlist,%str( )));

data P20040by01;

set Benchmar.P20040by0 (keep = date &varlist);

*set Benchmar.P20040by0;

array var (&nvar) &varlist;

array rvar (&nvar) &varlist;

array gvar (&nvar) &varlist;

array lgvar (&nvar) &varlist;

array ret (&nvar) &varlist;

do i = 1 to &nvar;

rvar(i) = (var(i+1)-lag(var(i+1)))/lag(var(i+1));

gvar(i) = rvar(i)+1;

lgvar(i) = lag(gvar(i));

end;

rgla = mean( of lgvar(*) );

do i = 1 to &nvar;

ret(i) = (rvar(i)*lgvar(i))/rgla;

end;

run;

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 17:20:02 GMT

Hi,

What is 'diff' in your code?

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 17:34:00 GMT

The name of the variable that you wanted to copy from the other table and subtract from every value.

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 17:37:39 GMT

You need to generate and post some example input data and the desired output data. For example here is data for 2 dates and 3 firms.

data one ;

informat date date9.;

format date date9.;

input date firm1-firm3 ;

cards;

01JAN2013 100 200 300

02JAN2013 101 210 330

run;

data two;

data one ;

informat date date9.;

format date date9.;

input date diff ;

cards;

01JAN2013 10

02JAN2013 20

run;

data want ;

informat date date9.;

format date date9.;

input date diff firm1-firm3 ;

cards;

01JAN2013 10 90 190 290

02JAN2013 20 81 190 310

run;

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 17:43:30 GMT

You will probably get results faster if you normalize your data.

So if you currently have data with 200 columns that represent PRICE for 200 firms you should convert it into 200 rows with one column for the FIRM and another for the PRICE.

proc transpose data=have(keep=date firm1-firm200) out=price(rename=(col1=PRICE _name_=FIRM)) ;

by price;

var firm1-firm200;

run;

proc sort; by firm date; run;

Then if you have another variable that also had collected for each FIRM/DATE combination you could easily merge them and do calculations.

data gain;

merge price basis ;

by firm date ;

gain = price - basis ;

run;

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 18:31:20 GMT

I am sorry I could not understand this. Is this for my previous question?

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 18:36:11 GMT

Just post some examples so we can understand more clearly what you are talking about.

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 19:07:51 GMT

I am attaching two files: Benchmark and usa_firms. I need to subtract the last column (average_ret) of Benchmark table from all return columns (ret1, ret2, .... , ret61) of usa_firms.

By the by, can I rename those variables (ret1, ret2, etc) by firm identity (such as US1234456, there are 61 firm identities in usa_firms table. I got these ret1, ret2 …. variable names when I calculated the returns of those firms).

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 19:47:21 GMT

So did you try the program I posted?

I notice that you do not seem to have any variable to uniquely identify the rows in the datasets. This should be corrected as you will have a hard time making sure that you are matching the rows without that.

So I took just the first 6 rows and two firm values so I can show you how this can be done. I just took the first two columns, I am not sure if that is what you are calling RET or not, but you should be able to adjust to use the variable names of your columns.

So here are two simple data steps to setup the sample datasets. BENCHMARK will have the AVERAGE_RET variable that you want to merge onto and then subtract. and USA_FIRMS will have the values that you will be subtracting from. I had the data steps define a ROWID variable so that I can make sure to match the rows properly. Also I renamed the DATE variables so I can compare and make sure that the dates from the two different files have not gotten out of line. Also I set the format on the date variables to yymmdd because it leads to less confusion about what is the month and what is the day, also it will sort properly if it is treated as text.

data benchmark;

informat date1 yymmdd10. ;

format date1 yymmdd10.;

rowid+1;

input date1 average_ret ;

cards4;

. .

2004/01/01 0

2004/02/01 0.0336863483

2004/05/01 0.0416289109

2004/06/01 -0.001851738

;;;;

data usa_firms;

informat date2 yymmdd10. ;

format date2 yymmdd10.;

rowid+1;

input date2 firm1 firm2;

cards4;

. 15.4467 7.82

. 15.06 7.76

2004/01/01 15.06 7.76

2004/02/01 14.9867 7.91

2004/05/01 14.6667 8.05

2004/06/01 13.2527 8.09

;;;;

Now you just need to merge the two datasets so that the variables you want to work with are in the same data vector.

I had it create new variables to old the subtracted values. But you could store the values back into the original variables.

Also I used the DO OVER construct for working with the arrrays. It makes typing the names easier to not have to add the indexes.

data want ;

merge benchmark usa_firms ;

by rowid;

if date1 ne date2 then put 'ERROR: DATE MISMATCH. ' date1= date2=;

else do;

array firms firm1-firm2 ;

array new new1-new2 ;

do over firms;

new=firms-average_ret;

end;

run;

So lets look at the results and see if it is what you expected.

data _null_;

set want ;

put rowid date1 new1 new2 ;

run;

1 . . .

2 . . .

3 2004-01-01 15.06 7.76

4 2004-02-01 14.953013652 7.8763136517

5 2004-05-01 14.625071089 8.0083710891

6 2004-06-01 13.254551738 8.091851738

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 20:03:40 GMT

How did you write the followings in the datastep?

. .

2004/01/01 0

2004/02/01 0.0336863483

2004/05/01 0.0416289109

2004/06/01 -0.001851738

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 20:50:01 GMT

Copy and pasted it from the dataset you posted. I opened the data set with SAS System Viewer, highlighted the first 6 values of the variable and hit control-C for copy, went to the program editor and hit control-V for paste. I then typed in the dates by hand.

But you don't need to type the data as you already have it in a dataset.

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 21:27:08 GMT

Do I need to install the SAS System Viewer?

You copied only first 6 values, but If I do this for the whole dataset then there will be 264 values. Isn't it too much?

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 22:08:52 GMT

I am not being very clear. The ONLY purpose of the simple data steps I posted was have some common example data that everyone reading the thread could see and play with. You already have data in a datesets, you do NOT need to copy and paste it!!!

For you to generate the same output I generated starting with the two datasets you posted you could run this program. The dataset options OBS=6 will limit it just using the first 6 rows so that it will look just like what I posted. Of course in realitiy you wouldn't use that option since you want to operate on all of the rows in your datasets.

data both ;

merge usa_firms(obs=6 keep=date US4583341098 US09061G1013)

benchmark(obs=6 keep=average_ret)

;

* NO BY STATEMENT since you do not have any id variable to use to merge ;

array firms US4583341098 US09061G1013 ;

array new new1 - new2 ;

do over firms;

new = firms - average_ret ;

end;

run;

Re: Copy column from one dataset to another

AbuChowdhury — Fri, 21 Feb 2014 23:06:55 GMT

It works now. But I had to change the date format:

data benchmark;

informat date1 yymmdd10. ;

format date1 yymmdd10.;

Instead of the above code, I wrote:

data benchmark;

informat date1 ddmmyy10. ;

format date1 ddmmyy10.;

since the date in my dataset was like dd/mm/2004. Am I right?

Your latest codes also work fine.

Thank you very much.

Re: Copy column from one dataset to another

Tom — Fri, 21 Feb 2014 23:43:26 GMT

Yes. The INFORMAT is what tells SAS how to convert text values. MMDDYY will read strings in month day year order .

Re: Copy column from one dataset to another

AbuChowdhury — Sat, 22 Feb 2014 10:43:04 GMT

What's the difference between date10. and date9. and between cards and cards4?

new1, new2, … etc. calculate the difference between returns from usa_firms table and average_ret from Benchmark table. I want perform the same task for another dataset (say uk_firms). After performing the same operation this newdataset will also show the same variables: new1, new2, … etc. I need to merge these two datasets: usa_firms and uk_firms. It's a problematic since both datasets have same variable names (new1, new2, … ) but those are from two different countries? How can I resolve this?

If you look at the dataset usa_firms, you will see the price data for US firms and US firms are identified as . There are price data for 61 US firms. From these price data, returns are calculated (ret1, ret2, …. (shown in the right end of the dataset)) for those 61 firms. By looking at ret1, ret2 variables I cannot understand which firms' return they are. Is it possible to use firm identity instead of ret1, ret2 variable names? I need to identify the returns for each firm because I need to do the same task for another dataset (uk_firms) and need to merge these two datasets.

Re: Copy column from one dataset to another

Tom — Sat, 22 Feb 2014 16:52:32 GMT

If you want to continue to use his horizontal format instead of a vertical format then you will need to continually be creating new variable names.

You can use some tricks to help you though. For example below SQL will create two macro variables with space delimited lists of variable names that would be very suited for use in a ARRAY statement. The first list is just the variables of interest and the second is the same but with _RETURN appended to the names.

proc sql noprint ;

select name

, cats(name,'_return')

into varlist separated by ' '

, retlist separated by ' '

from dictionary.columns

where libname = 'WORK' and memname = 'USA_FIRMS'

and indexw('US4583341098 US09061G1013',upcase(name))

;

quit;

data .... ;

array firms &varlist;

array ret &retlist;

....

run;