Hi,
I am having some challenge to do the following in SAS VA i.e, to generate MycarTable from CarTable.
Please guide me how to create a table(MyCarTable) with original columns and a new column that is a union of first two columns.
Thanks.
Best regards,
Ajay
Hi Ajay,
From the Visual Data Builder, try creating a data query and adding your table. After you add the existing columns by clicking on the names, create a new column by clicking the plus sign (+) on the Column Editor tab, and then create a union expression for the new column. Here's some documentation that may be helpful:
Best regards,
Lorrie
Hi Ajay,
I did some more research to see how we can get this to work, and it looks like the only way would be for you to modify the code directly. Here are some options and what the results would looks like:
For example:
data cars; keep Petrol Diesel; merge sashelp.cars (where=(Origin='Asia') obs=5 rename=(Model=Diesel)) sashelp.cars (where=(Origin='Europe')obs=8 rename=(Model=Petrol)); run;
Example original dataset:
------------------------------------------
Option 1:
proc ds2; TITLE 'Using DS2'; data; method run(); set {select * from cars full join (select Petrol as AllCars from cars union all select Diesel from cars ) as a on cars.Petrol=a.AllCars}; if not missing(cat(Petrol,Diesel,Allcars)); end; enddata; run; quit;
Option 1 results:
----------------------------------------------------------
Option 2:
proc sql; TITLE 'Using PROC SQL'; select * from cars full join (select Petrol as AllCars from cars union all select Diesel from cars ) as a on cars.Petrol=a.AllCars where cats(Petrol,Diesel,Allcars) is not null ; quit;
Option 2 results:
In the case that you wanted to have the data union appear in a seperate table (instead of combined with the original columns), the answer is a lot more straightforward. You would go to the data builder and click LASR > Append Tables to get started. Here is a link to more detailed information about appending tables:
Best regards,
Lorrie
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.