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
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
put the macro var in double quotes:
new_company="&comp_name.";
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
So simple. Thanks Reeza.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.