adding global variable to a dataset

[dan999]

I want to add data that I get from another table and store in a variable to every row in a dataset. The resulting table2 should have a new column called "new_company" with the text "Ford - BMC" in every row. Right now I get 3 new columns named new_company, BMC and Ford but all columns are blank.

data company;
	length co_name $50.;
 	input co_name $ 1-50; 
 	datalines;
Ford - BMC 
;
run;

data table1;
	infile datalines;
	input did pgm_id user $ dttime DATETIME24.3; 
	format dttime DATETIME24.3;
	return;
	datalines;
8 63832680 DAN 23AUG2013:19:42:55.160
2 63832680 JAN 01AUG2013:19:20:58.785
6 63832680 DAN 22AUG2013:13:57:12.506
4 63832680 DAN 13AUG2013:19:29:39.578
7 63832680 DAN 23AUG2013:15:30:00.452
9 63832680 DAN 26AUG2013:20:29:40.386
1 63832680 BOB 14AUG2013:22:24:41.894
5 63832680 DAN 16AUG2013:18:40:52.547
3 63832680 DAN 08AUG2013:19:13:00.356
;
run;

%GLOBAL comp_name;
proc sql noprint;
	select distinct co_name into :comp_name from company;
quit;
%put company &comp_name.;

data work.table2;
	set work.table1;
	new_company=&comp_name.;
run;

TIA

[dan999]

Sorry about the way the code looks. It's the first time I've used the feature to insert sas code and it doesn't seem to have worked.

[johnsville]

put the macro var in double quotes:

new_company="&comp_name.";

[Reeza · Accepted solution]

Fixed your code. The key is once you've modified it in the pop up editor, do not modify again in the editor. 

data work.table2;
	set work.table1;
	new_company=&comp_name.;
run;

This is the portion that's problematic. 

Look at it and see what SAS see's:

*What you programmed;
new_company=&comp_name.;
*What SAS sees;
new_company = Ford - BMC; *ford and bmc would be interpreted as variables here, check your log for a warning;

*What it should be;
new_company = "&comp_name";
*What SAS sees;
new_company = "Ford - BMC";

---

@dan999 wrote:

I want to add data that I get from another table and store in a variable to every row in a dataset. The resulting table2 should have a new column called "new_company" with the text "Ford - BMC" in every row. Right now I get 3 new columns named new_company, BMC and Ford but all columns are blank.

data company;
	length co_name $50.;
 	input co_name $ 1-50; 
 	datalines;
Ford - BMC 
;
run;

data table1;
	infile datalines;
	input did pgm_id user $ dttime DATETIME24.3; 
	format dttime DATETIME24.3;
	return;
	datalines;
8 63832680 DAN 23AUG2013:19:42:55.160
2 63832680 JAN 01AUG2013:19:20:58.785
6 63832680 DAN 22AUG2013:13:57:12.506
4 63832680 DAN 13AUG2013:19:29:39.578
7 63832680 DAN 23AUG2013:15:30:00.452
9 63832680 DAN 26AUG2013:20:29:40.386
1 63832680 BOB 14AUG2013:22:24:41.894
5 63832680 DAN 16AUG2013:18:40:52.547
3 63832680 DAN 08AUG2013:19:13:00.356
;
run;

%GLOBAL comp_name;
proc sql noprint;
	select distinct co_name into :comp_name from company;
quit;
%put company &comp_name.;

data work.table2;
	set work.table1;
	new_company=&comp_name.;
run;

TIA

---

[dan999]

So simple. Thanks Reeza.