Union on SAS VA

[vasireddyajay]

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

[Lorrie_SAS]

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:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n0bk4cm9klgbh3n1jxpb8...

Best regards,

Lorrie 

[vasireddyajay]

Hi Lorrie,

thank you for your comment. I tried to use the (+) but when I try to use select statement, in the expression, I am getting exceptions. Could you please let me know the expression you used to create a new column that joins the columns from the same table.

Thanks.

Kind regards,
Ajay

[Lorrie_SAS]

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:

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#n10uk49nxnzpmcn0zp4na...

Best regards,

Lorrie