How to Add .sas file to query

[Macy]

Hi, I have a probolem regarding sas file. 

i have a sas file and i want to add to query, to merge data, however it is no options to choose from home folder, only can used the one from library,but because myfile is .sas file, i cnnot add to library, then what shall i do the add it into query ? 

[Shmuel]

.sas file is aprogram file.

What do you want to do by adding it to a query?

What SAS platform are you using?

What do you have in your query? and what does the .sas file contains ?

[Macy]

Hi, I have a sas file called GermanCreditV2Combination.sas, in this file i combined several binary variables, then i want to link it to another data set to use these combined variables. 

So i create a query and try to link them together, but i find i have no option to attach .sas file. 

[Macy]

Hi, I have a sas file called GermanCreditV2Combination.sas, in this file i combined several binary variables, then i want to link it to another data set to use these combined variables. 

So i create a query and try to link them together, but i find i have no option to attach .sas file. 

[Macy]

another thing i forget to mention, i use sas studio. and i want to create analytical base table in sas, so i create query to do that.

then i try to link the combination sas file with the original data set to merge the binary variable, but i'm not quite sure whether my step is correct or not ? 

[Shmuel]

@Macy, in the posted .png files you show the output you have.

As .sas file is a program file it should be included in the CODE ,

maybe in as:

   %include "..path and name to the .sas file ..";

To help you more upload and post your query code and the .sas file code.

[Macy]

if i want to query ABT and create ABT, is the step i follow correct ? Becasue i am not quite sure. 

Or some other options i can choose to make it ? 

---

---

[Macy]

and i am trying to write code in query, but it does not allow me to do that, i donot know why. 

Attached is the sas file i want to merge with the data. 

[Macy]

Hi, 

The most important thing is i want to follow as attached process flow, as you can see in the middle bottom, it shows 'query ABT 'and upper middle it shows 'create ABT'. But firstly, i do not know how to query ABT and how to write code in qury, becuse i suppose in the query ABT i shall write the code to combine the different dataset tegether, but as i mentioned, it does not allow me to add .sas file. so i d not know how to add this file to query ABT. 

secondly, i have transformed the numerical variable, i want to include them in the ABT, but i donot know how to include thme into ABT, shall i write the code in query ? 

[Shmuel]

Now I can see that you created the work.car output dataset using proc sql.

You did not post the code you have in GermanCreditV2Combination.sas program file.

Post it and i will show you how to chain it to you query, the proc sql you have.

[Macy]

hi,

here is the code use to create the combination data

PROC SQL;
CREATE TABLE Work.Car
AS
SELECT OBS, GermanCreditV2.New_Car,GermanCreditV2.Used_Car
,(CASE
WHEN (New_Car = 1 or Used_Car = 1) THEN 1
ELSE 0
END) as Car
,(CASE
WHEN (MaleDiv = 1 or MaleSingle = 1 or MaleMarorWid = 1) THEN 1
ELSE 0
End) as Sex
,(CASE
WHEN (Education = 1 or Retraining = 1) THEN 1
ELSE 0
End) as NewEducation
,(case
WHEN (Guarantor = 1 or APPLICANT = 1) THEN 1
ELSE 0
END) AS NewApplicant
,(CASE
WHEN (Furniture = 1 or Radio = 1) THEN 1
ELSE 0
END) AS Appliance
FROM GC.GermanCreditV2;
QUIT;

[Shmuel]

I'm trying to understand the situation:

Your first code is - GermanCreditV2Combination.sas:

PROC SQL;
CREATE TABLE Work.Car
AS
SELECT  OBS, GermanCreditV2.New_Car,GermanCreditV2.Used_Car
,(CASE
     WHEN (New_Car = 1 or Used_Car = 1) THEN 1
     ELSE 0
     END) as Car
     ,(CASE
     WHEN (MaleDiv = 1 or MaleSingle = 1 or MaleMarorWid = 1) THEN 1
     ELSE 0
     End) as Sex
     ,(CASE
     WHEN (Education = 1 or Retraining = 1) THEN 1
     ELSE 0
     End) as NewEducation
     ,(case
     WHEN (Guarantor = 1 or APPLICANT = 1) THEN 1
     ELSE 0
     END) AS NewApplicant
     ,(CASE
     WHEN (Furniture = 1 or Radio = 1) THEN 1
     ELSE 0
     END) AS Appliance
     FROM GC.GermanCreditV2;
QUIT;

you wrote:

I have a sas file called GermanCreditV2Combination.sas, 
in this file i combined several binary variables, 
then i want to link it to another data set to use these combined variables.

You should distinguish between a program (proc sql or data set) and a sas dataset (like work.car).

What do you mean by 

i want to link it to another data set

what is this "another data set" ? what variables are in it ? what variables are in common with work.cars dataset ?

[Macy]

oh, i misunderstand it. j ust figure out and query ABT as attached, i merge these two datasets together.

Now i want to create ABT, do you know how to do that, and how shall i link the trasform data with ABT ? 

[Shmuel]

So you have merged your two datasets.

You are coming back to another your post:

https://communities.sas.com/t5/SAS-Studio/How-to-create-ABT-in-Sas-Studio/m-p/314621

I'm not expert in analytics. Someone else in the forum will help you.

[Macy]

hi,

here is the code use to create the combination data

PROC SQL;
CREATE TABLE Work.Car
AS
SELECT OBS, GermanCreditV2.New_Car,GermanCreditV2.Used_Car
,(CASE
WHEN (New_Car = 1 or Used_Car = 1) THEN 1
ELSE 0
END) as Car
,(CASE
WHEN (MaleDiv = 1 or MaleSingle = 1 or MaleMarorWid = 1) THEN 1
ELSE 0
End) as Sex
,(CASE
WHEN (Education = 1 or Retraining = 1) THEN 1
ELSE 0
End) as NewEducation
,(case
WHEN (Guarantor = 1 or APPLICANT = 1) THEN 1
ELSE 0
END) AS NewApplicant
,(CASE
WHEN (Furniture = 1 or Radio = 1) THEN 1
ELSE 0
END) AS Appliance
FROM GC.GermanCreditV2;
QUIT;